Problem with spatial index PostGis

Question

I'm using Postgis 2.1.

I have this table with about 50k rows:

CREATE TABLE rilevazioni
(
 geom geometry(Polygon),
 stimolo integer,
 tmstmp timestamp without time zone,
 id bigserial NOT NULL,
 CONSTRAINT rilevazioni_pkey PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE rilevazioni
 OWNER TO postgres;

and this spatial index

CREATE INDEX spatial_index_geom ON rilevazioni USING gist(geom);

If I run this query:

SELECT stimolo 
FROM rilevazioni
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

the execution time is 500ms.

"Bitmap Heap Scan on rilevazioni (cost=429.40..4570.96 rows=3742 width=4) (actual time=18.717..521.334 rows=56126 loops=1)"
" Recheck Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)"
" Filter: _st_intersects(geom, '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)"
" Heap Blocks: exact=1195"
" -> Bitmap Index Scan on spatial_index_geom (cost=0.00..428.46 rows=11225 width=0) (actual time=18.430..18.430 rows=56126 loops=1)"
" Index Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)"
"Planning time: 0.223 ms"
"Execution time: 528.747 ms"

If a drop the spatial index, the execution time for the same query is anyhow 500ms.

"Seq Scan on rilevazioni (cost=0.00..15928.08 rows=3742 width=4) (actual time=0.073..523.207 rows=56126 loops=1)"
" Filter: ((geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry) AND _st_intersects(geom, '0103000000 (...)"
"Planning time: 0.273 ms"
"Execution time: 531.010 ms"

What's the problem?

• John Powell: See this answer. The problem is having a constant in the ST_Intersects, so the index doesn't kick in.
• fnlls10: Thanks for the reply. So I if have to retrieve all polygons that intersect with a given polygon, the index doesn't work? There's a way to speed up query like that?
• John Powell: No it does. You just have to write the query as in the link. Refer to table in from and then alias.geom in St _ intersects. Then spatial index will work
• fnlls10: but if the polygon POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)) of which I want to know all the intersections with polygons stored in the db,is not in any table, but it's an external random parameter?
• Vince: So you're getting subsecond response on a small table returning a large subset with all columns, and you're complaining about the performance? Are you sure the unindexed time isn't just due to a hot cache?

Answer 1

The problem is that your query is completely non-selective, it's returning all the rows in the table. The only mystery is why the planner attempted to use the index at all, since usually it'll avoid it if your query isn't selective enough. Probably the data is brand new and if you run ANALYZE the planner will stop using the index at all. I'm a little surprised all your data fall inside a unit square, that's also a bit suspicious. Anyways, get a query box that returns a small fraction of your data, and you should see the spatial index working in your favour.