Postgis not using index scan only

Question

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 !

Answer 1

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.

Answer 2

I also can't get it to do the IOS, not even with the INCLUDE (which becomes supported by GiST in v12).

However it looks to me like all the time is going on the CPU in checking your geometry column against your monster geojson, which I think still has to be done even if you were able to get an index-only scan. So even if you could get it to use an index-only scan, it might not actually help you.

Answer 3

As mentioned in another answer, the index-only scan cannot be performed because not all of the data exists in the index to answer the query.

Since Postgis's GiST indexes work by comparing the overlap of bounding boxes, the query will always need to fetch the actual geometries from the table in order to compare their actual intersections.

What you can do to get an index-only scan, is include those geometries on the index.

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

Now the index contains all of the data it needs and doesn't need to return to the heap to get the geometry.

Worth noting that this could drastically increase the size of your index, so be cautious with that.