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?

See this answer. The problem is having a constant in the ST_Intersects, so the index doesn't kick in. — John Powell
– John Powell, Commented Jun 3, 2015 at 9:16
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? — fnlls10
– fnlls10, Commented Jun 3, 2015 at 9:30
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 — John Powell
– John Powell, Commented Jun 3, 2015 at 9:33
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? — fnlls10
– fnlls10, Commented Jun 3, 2015 at 9:57
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? — Vince
– Vince, Commented Jun 3, 2015 at 10:49

Paul Ramsey · Accepted Answer · 2015-06-04 19:51:04Z

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.

Stack Exchange Network

Problem with spatial index PostGis

1 Answer 1

Your Answer

Hot Network Questions

Problem with spatial index PostGis

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions