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

[Andy Colson]

On 12/16/2014 9:18 AM, Casper Børgesen (CABO) wrote:
> 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?
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>


I doubt changing postgresql.conf options will have any affect.

Its the st_srid(geom) call that's a problem.

Remove it, or specify the integer value.  The function call messes it up.

This should work:


explain analyze
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))')


-Andy