[postgis-users] Why won’t my SELECT query use the INDEX?

[Casper Børgesen (CABO)]

This seems like one of the most asked questions in the PostgreSQL world, but I guess I haven’t understood all the answers yet:

Why won’t my SELECT query use the INDEX I have created for it?

I have a table with about 18mio rows.

My SELECT statement looks like this:

SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"
FROM my_table
WHERE geom &&
ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))', ST_SRID("geom"))

The EXPLAIN ANALYZE of the above statement returned this:

"Seq Scan on my_table  (cost=0.00..4329124.83 rows=1731 width=1700) (actual time=194785.745..1553525.244 rows=138 loops=1)"
"  Filter: (geom && st_geomfromtext('POLYGON ((892267.1937422 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))'::text, st_srid(geom)))"
"  Rows Removed by Filter: 17311187"
"Total runtime: 1553525.352 ms"

The POLYGON described above is located at the outer edge of the 17mio geometries and the extent is pretty small.

I have executed a VACUUM ANALYZE to clean up the statistics, which didn’t seem to improve the results.

My INDEX has been created like this:

CREATE INDEX my_table_geom_idx
  ON my_table
  USING gist
  (geom);

Upon reading up on this issue I have changed the following in my postgresql.conf:

random_page_cost = 2.0
shared_buffers = 512MB
work_mem = 8MB
maintenance_work_mem = 256MB
effective_cache_size = 8GB

The HARDWARE is a Windows 2012R2 with 12GB ram and SAS HDDs. PostgreSQL 9.3 x64 and PostGIS 2.1.1.

Can any ask me the right questions so I can solve my INDEX problem?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141216/a4decac5/attachment.html>