Partial GiST Indexes in PostGIS

-- Verify PostGIS is installed and at a supported version
SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('postgis', 'postgis_topology');
-- Expect: postgis 3.2+ and, if topology is used, postgis_topology 3.2+

-- Confirm PostgreSQL version (14+ recommended for improved partial predicate evaluation)
SELECT version();

-- Confirm SRID on the target column
SELECT f_table_schema, f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'assets';
-- Expected: srid = 4326 (or your projected CRS), not 0

-- Confirm a baseline GiST index is present (or confirm none exists yet)
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'assets'
  AND indexdef ILIKE '%using gist%';

-- Identify frequent spatial queries with stable non-spatial predicates
SELECT
    query,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((mean_exec_time * calls)::numeric, 0) AS total_ms,
    rows
FROM pg_stat_statements
WHERE query ILIKE '%ST_Intersects%'
   OR query ILIKE '%ST_DWithin%'
   OR query ILIKE '% && %'
ORDER BY mean_exec_time * calls DESC
LIMIT 20;

-- Production partial GiST index — created CONCURRENTLY to avoid table locks
CREATE INDEX CONCURRENTLY idx_assets_active_geom
ON public.assets
USING GIST (geom)
WHERE status = 'active'
  AND tenant_id = 42;

-- For multi-tenant schemas, create per-tenant or per-region indexes
-- when a single high-traffic tenant dominates query load
CREATE INDEX CONCURRENTLY idx_assets_eu_central_geom
ON public.assets
USING GIST (geom)
WHERE status = 'active'
  AND region_code = 'EU-CENTRAL';

from sqlalchemy import select, and_
from sqlalchemy.orm import Session, DeclarativeBase, mapped_column
from geoalchemy2 import Geometry
from geoalchemy2.functions import ST_Intersects, ST_GeomFromText
from sqlalchemy import Integer, String

class Base(DeclarativeBase):
    pass

class Asset(Base):
    __tablename__ = "assets"
    id: int = mapped_column(Integer, primary_key=True)
    status: str = mapped_column(String)
    tenant_id: int = mapped_column(Integer)
    geom = mapped_column(Geometry("GEOMETRY", srid=4326))

def fetch_active_assets_in_bbox(
    session: Session,
    tenant_id: int,
    bbox_wkt: str,
) -> list[Asset]:
    # status and tenant_id predicates must match the partial index definition exactly
    stmt = (
        select(Asset)
        .where(
            Asset.status == "active",           # must match index predicate literal
            Asset.tenant_id == tenant_id,       # matched by PostgreSQL for this tenant
            ST_Intersects(
                Asset.geom,
                ST_GeomFromText(bbox_wkt, 4326),
            ),
        )
    )
    return list(session.scalars(stmt))

import psycopg
from psycopg.rows import dict_row

def query_active_geometries(
    conn: psycopg.Connection,
    tenant_id: int,
    bbox_wkt: str,
) -> list[dict]:
    with conn.cursor(row_factory=dict_row) as cur:
        cur.execute(
            """
            SELECT id, name, ST_AsText(geom) AS geom_wkt
            FROM public.assets
            WHERE status = %s              -- must be the literal 'active' to match predicate
              AND tenant_id = %s
              AND geom && ST_GeomFromText(%s, 4326)
            """,
            ("active", tenant_id, bbox_wkt),
        )
        return cur.fetchall()

import json
import psycopg

def verify_partial_index_usage(
    conn: psycopg.Connection,
    query: str,
    params: tuple = (),
    expected_index: str = "idx_assets_active_geom",
) -> dict:
    explain_sql = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query}"
    with conn.cursor() as cur:
        cur.execute(explain_sql, params)
        plan = cur.fetchone()[0]   # returns list[dict] in JSON mode

    def collect_index_scans(node: dict, acc: list) -> None:
        if "Index Name" in node:
            acc.append({
                "index": node["Index Name"],
                "node_type": node.get("Node Type"),
                "actual_rows": node.get("Actual Rows"),
                "recheck_removed": node.get("Rows Removed by Index Recheck", 0),
            })
        for child in node.get("Plans", []):
            collect_index_scans(child, acc)

    scans: list[dict] = []
    collect_index_scans(plan[0]["Plan"], scans)

    return {
        "index_used": any(s["index"] == expected_index for s in scans),
        "scans": scans,
        "planning_time_ms": plan[0].get("Planning Time"),
        "execution_time_ms": plan[0].get("Execution Time"),
    }

-- Verify GUC values for the current session
SHOW random_page_cost;
SHOW effective_cache_size;

-- Update at the table level if you cannot change postgresql.conf
ALTER TABLE public.assets SET (autovacuum_analyze_scale_factor = 0.02);

-- Refresh statistics on the target table after bulk operations
ANALYZE public.assets;

-- For very large tables, analyse only the relevant column to save time
ANALYZE public.assets (geom, status, tenant_id);

-- Check whether the partial index is being considered
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, ST_AsText(geom)
FROM public.assets
WHERE status = 'active'
  AND tenant_id = 42
  AND geom && ST_GeomFromText(
      'POLYGON((10 45, 11 45, 11 46, 10 46, 10 45))', 4326
  );

ALTER TABLE public.assets ALTER COLUMN status SET STATISTICS 200;
ANALYZE public.assets (status);

-- Check the SRID of the geometry you are passing
SELECT ST_SRID(ST_GeomFromText('POLYGON((...))', 0));  -- SRID 0 = unknown

-- Force the correct SRID in your query
SELECT ST_SRID(geom) FROM public.assets LIMIT 1;
-- Then use that SRID in your application queries

-- Create replacement index for expanded predicate
CREATE INDEX CONCURRENTLY idx_assets_active_pending_geom
ON public.assets
USING GIST (geom)
WHERE status IN ('active', 'pending')
  AND tenant_id = 42;

-- After verifying the planner uses the new index, drop the old one
DROP INDEX CONCURRENTLY idx_assets_active_geom;

import psycopg
from psycopg.rows import dict_row

def stream_active_geometries(
    conn: psycopg.Connection,
    tenant_id: int,
    bbox_wkt: str,
    batch_size: int = 500,
):
    with conn.cursor(row_factory=dict_row) as cur:
        cur.itersize = batch_size  # server-side cursor chunk size
        cur.execute(
            """
            SELECT id, name, ST_AsText(geom) AS geom_wkt
            FROM public.assets
            WHERE status = %s
              AND tenant_id = %s
              AND geom && ST_GeomFromText(%s, 4326)
            """,
            ("active", tenant_id, bbox_wkt),
        )
        for row in cur:  # yields one batch at a time, not all rows at once
            yield row

-- Step 1: create the next-window index CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_assets_active_2026q3
ON public.assets
USING GIST (geom)
WHERE status = 'active'
  AND created_at >= '2026-07-01'::timestamptz
  AND created_at <  '2026-10-01'::timestamptz;

-- Step 2: after verifying planner adoption, retire the previous window
DROP INDEX CONCURRENTLY idx_assets_active_2026q2;

-- Find partial GiST indexes with zero or near-zero usage since last stats reset
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_indexes USING (schemaname, tablename, indexname)
WHERE indexdef ILIKE '%using gist%'
  AND indexdef ILIKE '%where%'   -- partial indexes only
ORDER BY idx_scan ASC;

-- Confirm the index is genuinely unused, not just cold after a stats reset
SELECT pg_stat_reset_single_table_counters('public.assets'::regclass::oid);
-- Wait for one full traffic cycle (e.g. 24 h), then re-query pg_stat_user_indexes

-- 1. Confirm the index appears in pg_indexes with its predicate
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'assets'
  AND indexname = 'idx_assets_active_geom';

-- 2. Run EXPLAIN to verify Index Scan (not Seq Scan)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id
FROM public.assets
WHERE status = 'active'
  AND tenant_id = 42
  AND geom && ST_GeomFromText(
      'POLYGON((10.0 45.0, 11.0 45.0, 11.0 46.0, 10.0 46.0, 10.0 45.0))',
      4326
  );

-- 3. Check idx_scan counter increments after a few query runs
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE indexname = 'idx_assets_active_geom';