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>: > > 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 >> 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 > 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