0

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 Jun 3 '15 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 Jun 3 '15 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 Jun 3 '15 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 Jun 3 '15 at 9:57
  • 1
    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 Jun 3 '15 at 10:49
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.