Hi, You may see one problem described in the postgis doc regarding planner selectivity: Your table is small enough to let the planner think a seq scan will be always faster than an index scan, despite the size of the geometric column. You can force the index usage by setting a parameter before running the query: Set enable_seq_scan to on; And see if it changes the Query plan.
Nicolas Le 15 juil. 2013 à 17:30, "Daniel, de la Cuesta Navarrete" <[email protected]> a écrit : > Hi, > > I have a table with a geometry column and around 3000 rows. > > The table has a geospatial index in the geometry column (service_area): > > CREATE INDEX regions_index ON regions USING GIST ( service_area ); > > I am doing the following query: > > SELECT "regions".* FROM "regions" WHERE (regions.service_area && > ST_GeographyFromText('SRID=4326;POINT(7.04756999999995 51.17042)')) ORDER BY > admin_area_level DESC, fare_quality DESC, is_operation_region DESC LIMIT 1 > > It is taking around 8-9 seconds to perform this query but the problem is that > it is not taking the geospatial index, if a do EXPLAIN of the query I get: > > > Limit (cost=138.23..138.24 rows=1 width=130769) > -> Sort (cost=138.23..138.24 rows=1 width=130769) > > Sort Key: admin_area_level, fare_quality, is_operation_region > -> Seq Scan on regions (cost=0.00..138.22 rows=1 width=130769) > > Filter: ((service_area)::geography && > '0101000020E6100000FF14A930B6301C40CB4A9352D0954940'::geography) > > Is it possible to improve the performance of this query? Why is not taking > the geospatial index? > > > PostgreSQL 9.1.4 on i686-pc-linux-gnu, compiled by gcc (Ubuntu > 4.4.3-4ubuntu5.1) 4.4.3, 32-bit POSTGIS=\"1.5.3\" GEOS=\"3.3.1-CAPI-1.7.1\" > PROJ=\"Rel. 4.7.1, 23 September 2009\" LIBXML=\"2.7.6\" USE_STATS" > > Best > D > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
