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-boun...@lists.osgeo.org [mailto:postgis-users-boun...@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.c...@gmail.com<mailto:remi.c...@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 <a...@squeakycode.net<mailto:a...@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@lists.osgeo.org<mailto:postgis-users@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@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
