[postgis-users] Geospatial index is not used

[Bborie Park]

Your service_area geometries are having to be converted to geography for
that geography comparison. The index can't be used because the index is for
a geometry.

A valid approach is to create a geography index...

CREATE INDEX regions_index ON regions USING GIST ( service_area::geography
);

And then run your query.

A correct approach is to change your query. Instead of using
ST_GeographyFromText()...

SELECT "regions".* FROM "regions" WHERE (regions.service_area &&
ST_GeomFromEWKT('SRID=4326;POINT(7.04756999999995 51.17042)') ORDER BY
admin_area_level DESC, fare_quality DESC, is_operation_region DESC LIMIT 1

-bborie


On Mon, Jul 15, 2013 at 8:30 AM, Daniel, de la Cuesta Navarrete <
cues7a at gmail.com> wrote:

> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130715/1738b601/attachment.html>