<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>Hi,</div><div>You may see one problem described in the postgis doc regarding planner selectivity:</div><div>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.</div><div>You can force the index usage by setting a parameter before running the query:</div><div>Set enable_seq_scan to on;</div><div>And see if it changes the Query plan.</div><div><br></div><div>Nicolas <br><div><br></div></div><div><br>Le 15 juil. 2013 à 17:30, "Daniel, de la Cuesta Navarrete" <<a href="mailto:cues7a@gmail.com">cues7a@gmail.com</a>> a écrit :<br><br></div><blockquote type="cite"><div><div dir="ltr"><div>Hi,</div><div><br></div><div>I have a table with a geometry column and around 3000 rows.</div><div><br></div><div>The table has a geospatial index in the geometry column (service_area):</div><div><br></div>
<div><font face="courier new, monospace">CREATE INDEX regions_index ON regions USING GIST ( service_area );</font></div><div><br></div><div>I am doing the following query:</div><div><br></div><div><font face="courier new, monospace">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</font></div>
<div><br></div><div>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:</div><div><br></div><div><br></div><div><font face="courier new, monospace">Limit (cost=138.23..138.24 rows=1 width=130769)</font></div>
<div><font face="courier new, monospace"> -> Sort (cost=138.23..138.24 rows=1 width=130769)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace"> Sort Key: admin_area_level, fare_quality, is_operation_region</font></div>
<div><font face="courier new, monospace"> -> Seq Scan on regions (cost=0.00..138.22 rows=1 width=130769)</font></div><div><font face="courier new, monospace"> </font></div><div><font face="courier new, monospace"> Filter: ((service_area)::geography && '0101000020E6100000FF14A930B6301C40CB4A9352D0954940'::geography)</font></div>
<div><br></div><div>Is it possible to improve the performance of this query? Why is not taking the geospatial index?</div><div><br></div><div><br></div><div><font face="courier new, monospace">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"</font><br>
</div><div><br></div><div>Best</div><div>D</div><div><br></div></div>
</div></blockquote><blockquote type="cite"><div><span>_______________________________________________</span><br><span>postgis-users mailing list</span><br><span><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a></span><br><span><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></span><br></div></blockquote></body></html>