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
