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