[postgis-users] Geospatial index is not used

[Daniel, de la Cuesta Navarrete]

Thanks for your answer,

The first one by Stephen worked really well,

Just changing  from

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

to

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

solved the problem, now is very fast,

Thanks!


On 15 July 2013 17:37, Sandro Santilli <strk at keybit.net> wrote:

> Add a spatial index on service_area too, then run ANALYZE.
>
> Use EXPLAIN ANALYZE <your_query> next time, if needed.
>
> --strk;
>
> On Mon, Jul 15, 2013 at 05:30:57PM +0200, Daniel, de la Cuesta Navarrete
> 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/670085b0/attachment.html>