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

Wed Dec 17 04:11:36 PST 2014

Hi Rémi and Andy.

Thanks for your quick and simple but specific answer. It definitely helped me along the way toward a final solution.

Regards, Casper

From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: 16. december 2014 17:50
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Why won’t my SELECT query use the INDEX?

For this particular case,
using st_srid(geom) means that the planner have to compute this value for each line .
(the planner has no way to know that srid is the same for every geometry in the column, this might not be the case)
You can't use it .
for instance, you could simply use plpgsql to get the srid, and create your querry directly with the number
(your querry looks like it is fabricated programmatically anyway)
You could also look for this srid value in the geometry_columns view, but it would be less safe.
lastly you coudl had an index on your geom table casted to srid 0, this way you could systematically cast your wkt to srid 0. I would call it a pretty bad idea unless you have lot's of differnt srid and you will have to translate for other usages anyway
Example :
CREATE INDEX ON my_table USING GIST( st_translate(geom,0) )
SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"
FROM my_table
WHERE st_translate(geom,0) &&
ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))',0)
Cheers,
Rémi-C

2014-12-16 17:43 GMT+01:00 Rémi Cura <remi.cura at gmail.com<mailto:remi.cura at gmail.com>>:
Hey,
no index can come from 2 problems :
  - your querry can't use the index
  - your querry could use the index but the planner thinks it's faster to do sequential scan.
You can discriminate between the 2 avoiding explicitly sequential scan :
`SET enable_seqscan TO FALSE`
(don't forget to turn it back one afterward)
if you are still not using index, your querry can't use it at all, thus there is a problem of definition.
Cheers,
Rémi-C

2014-12-16 17:23 GMT+01:00 Andy Colson <andy at squeakycode.net<mailto:andy at squeakycode.net>>:
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<mailto: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

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141217/ed241501/attachment.html>