3

I have an odd case where creating a GIST index slows down my query instead of speeding it up.

The setup consists of two tables, one with multi-polygons, another with points. I want to calculate which polygons contain which points. My query is:

create table as test
select polygons.name, points.name
from polygons, points where st_contains(polygons.geom, points.location);

To clarify, geom is of type st_multipolygon, location is of type st_point. I make sure to perform a vacuum analyze on tables after creating or deleting indexes. With timing on I get:

no indexes: 14784 ms, index on geom: 79849 ms, index on geom, location: 3826 ms

How could this be? If I understand correctly, GIST uses bounding boxes to speedup the st_contains function. Hence, it only makes sense to create bounding boxes for polygon geometries, right? My statement for making a GIST index is

create index poly_index on polygons using gist(geom)

and for points

create index point_index on points using gist(location)

checking queries with the explain keyword tells me the index is used properly. As I'm new to Postgis, with the information available, I'm really confused whether this is normal behaviour or whether it's an error on my side. Lastly, my PostgreSQL version is 9.5.2, PostGIS version 2.2.2 (GEOS: 3.4.2, PROJ: 4.9.1, GDAL: 1.11.2)

Explain results (polygons = top500, points = data_items):

No indexes (14784 ms)

Nested Loop  (cost=0.00..685544.56 rows=23820 width=25)
   Join Filter: ((top500.geom && data_items.location) AND _st_intersects(top500.geom, data_items.location))
   ->  Seq Scan on top500  (cost=0.00..231.59 rows=859 width=52033)
   ->  Materialize  (cost=0.00..139.15 rows=3010 width=49)
         ->  Seq Scan on data_items  (cost=0.00..124.10 rows=3010 width=49)

Index only on polygons (79849 ms)

Nested Loop  (cost=0.14..2308.30 rows=23820 width=25)
   ->  Seq Scan on data_items  (cost=0.00..124.10 rows=3010 width=49)
   ->  Index Scan using poly_index on top500  (cost=0.14..0.72 rows=1 width=52033)
         Index Cond: (geom && data_items.location)
         Filter: _st_intersects(geom, data_items.location)

Index only on points (3822 ms)

Nested Loop  (cost=0.15..1084.66 rows=23820 width=25)
   ->  Seq Scan on top500  (cost=0.00..231.59 rows=859 width=52033)
   ->  Index Scan using item_index on data_items  (cost=0.15..0.98 rows=1 width=49)
         Index Cond: (top500.geom && location)
         Filter: _st_intersects(top500.geom, location)

Index on everything (3826 ms)

Nested Loop  (cost=0.15..1084.66 rows=23820 width=25)
   ->  Seq Scan on top500  (cost=0.00..231.59 rows=859 width=52033)
   ->  Index Scan using item_index on data_items  (cost=0.15..0.98 rows=1 width=49)
         Index Cond: (top500.geom && location)
         Filter: _st_intersects(top500.geom, location)
  • You are right that bounding box of a point is actually a point. But the point is that if you do not create point_index you do not have an index at all. It is interesting why creating index only for the polygons makes the query slower. However, the right way is to create indexes for both points and polygons and it gives clearly fastest result, so who cares? – user30184 May 9 '16 at 10:24
  • If the polygons are large enough, relative to the data envelope, they won't provide any useful selectivity (and therefore could slow access) – Vince May 9 '16 at 11:09
  • In this case I believe that the polygon index is selective because the "both indexes" option is five times faster than "no indexes". – user30184 May 9 '16 at 11:13
  • Who cares ? me for example :). Would be nice to understand perfectly those results... and sadly I don't... – WKT May 9 '16 at 11:21
  • Row counts aren't provided, and one case is missing (index only on location). The gain may be from inverting to a within test using the location index. – Vince May 9 '16 at 11:23
2

The answer is right in your explain results. When you put the index on the polygons, the planner thinks: "aha, I can do a nested loop on the points and quickly get each polygon that is of interest for the points".

This ends up being wrong, probably because you (a) have more points than polygons and (b) evaluating the index condition is only the cheapest part of the join. The expensive part is doing the point-in-poly test, which is optimized in PostGIS for cases where the nested loop is driven from the polygon table.

Hence, when you reverse things, and have an index on the points, but not on the polygons, things get nice and fast, as the nested loop is driven off the polygon table, with fast indexed searches into the points table, along with optimized cached point-in-poly calculations for the exact containment.

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.