postgresqlindexingpostgiscomposite-keycovering-index

Postgis not using index scan only


I'm trying to make Postgis using an index scan only but instead it's performing a Bitmap index scan into a Bitmap heap scan.

I got the following table - containing 50k rows:

CREATE TABLE IF NOT EXISTS public.forme_iris
(
    code_iris character varying(20) COLLATE pg_catalog."default" NOT NULL,
    geometry geometry,
    CONSTRAINT forme_iris_pkey PRIMARY KEY (code_iris)
)

I've created this index:

CREATE INDEX forme_iris_geometry_idx1
  ON public.forme_iris USING gist
  (geometry, code_iris)
  TABLESPACE pg_default;

I couldn't use a proper covering index with INCLUDE statement, Postgis tells me that's not supported.

Performed request:

SELECT geometry, code_iris
  FROM forme_iris iris
  WHERE ST_Intersects(iris.geometry, ST_SetSrid(ST_GeomFromGeoJson('<geojson>'), 4326))

It returns 821 rows, I've vacuumed + analyzed the table before performing the request.

PostgreSQL version: PostgreSQL 11.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

Postgis version: 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

EXPLAIN ANALYZE output: https://explain.dalibo.com/plan/TJQt

Thanks !


Solution

  • PostGIS GiST indexes compress the values (they store a bounding box) and have no "fetch" method, so they cannot do index-only scans:

    SELECT opf.opfname,
           amp.amprocnum,
           amp.amproc::regproc
    FROM pg_opfamily AS opf
       JOIN pg_amproc AS amp ON opf.oid = amp.amprocfamily
       JOIN pg_am ON opf.opfmethod = pg_am.oid
    WHERE pg_am.amname = 'gist'
      AND amp.amprocnum IN (3, 9)  -- 3 is "compress", 9 is "fetch"
      AND opf.opfname LIKE '%geometry%';
    
           opfname        │ amprocnum │          amproc           
    ══════════════════════╪═══════════╪═══════════════════════════
     gist_geometry_ops_2d │         3 │ geometry_gist_compress_2d
     gist_geometry_ops_nd │         3 │ geometry_gist_compress_nd
    (2 rows)
    

    See the documentation:

    There are five methods that an index operator class for GiST must provide, and six that are optional. [...]

    compress

    Converts a data item into a format suitable for physical storage in an index page. If the compress method is omitted, data items are stored in the index without modification. [...]

    fetch

    Converts the compressed index representation of a data item into the original data type, for index-only scans. The returned data must be an exact, non-lossy copy of the originally indexed value.

    The documentation doesn't cover what the method numbers for these are, you have to consult the source for that.