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 !
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.