GIST slows down query?

Question

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)

Answer 1

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.