[postgis-users] Why postgis doesn't use index during query?

Nicolas Ribot nicolas.ribot at gmail.com
Tue Jan 22 09:24:57 PST 2013 

Hi,

You could also look at the new st_dwithin operator to filter out possible
candidates efficiently.

Nicolas


On 22 January 2013 18:07, Carsten Hogertz <carsten.hogertz at gmail.com> wrote:

> Thanks Francois,
> as I am a newbie, could you please help me a bit with that idea? How
> should i do? How do I calculate the X?
>
> Thank you.
>
>
> 2013/1/22 Francois Hugues <hugues.francois at irstea.fr>
>
>> Hello,
>>
>> In the with of your query, you are searching for the min distance and you
>> calculate all distances between every polygons. Try add a where clause
>> st_distance<  X to avoid unuseful calcul (you should find the max of the
>> min to set X value). This may improve performances.
>>
>> Hugues
>>
>> Carsten Hogertz <carsten.hogertz at gmail.com> a écrit :
>>
>> I (maybe) have a similar problem.
>> Could I rewrite something in my statement to make it faster?
>>
>> with dist_min as (
>> select h.gid, min(st_distance(h.geom, i.geom)) dist_min from hotels_150000
>> h, iata_codes i
>> where h.l_iso_a2 = i.ctry
>> group by h.gid
>> )
>> select h.h_key, h.gid, i.loc, i.gid, d.dist_min from dist_min d,
>> hotels_150000 h, iata_codes i
>> where st_distance(h.geom, i.geom) = dist_min
>> order by h.h_key
>>
>> thanks
>> --
>> carsten
>>
>>
>> 2013/1/22 Nicolas Ribot <nicolas.ribot at gmail.com>
>>
>> > (sorry, a little precision: All st_* predicates internally use the &&
>> > GIST operator: bbox intersects)
>> >
>> >
>> > On 22 January 2013 16:44, Nicolas Ribot <nicolas.ribot at gmail.com>
>> wrote:
>> >
>> >> In PSQL, \do will list you installed operators:
>> >>
>> >> \do+
>> >>                                 List of operators
>> >>  Schema | Name | Left arg type | Right arg type |   Result type    |
>> >> Description
>> >>
>> >>
>> --------+------+---------------+----------------+------------------+-------------
>> >>  public | &&   | geography     | geography      | boolean          |
>> >>  public | &&   | geometry      | geometry       | boolean          |
>> >>  public | &&   | geometry      | raster         | boolean          |
>> >>  public | &&   | raster        | geometry       | boolean          |
>> >>  public | &&   | raster        | raster         | boolean          |
>> >>  public | &&&  | geometry      | geometry       | boolean          |
>> >>  public | &<   | geometry      | geometry       | boolean          |
>> >>  public | &<   | raster        | raster         | boolean          |
>> >>  public | &<|  | geometry      | geometry       | boolean          |
>> >>  public | &<|  | raster        | raster         | boolean          |
>> >>  public | &>   | geometry      | geometry       | boolean          |
>> >>  public | &>   | raster        | raster         | boolean          |
>> >>  public | <    | geography     | geography      | boolean          |
>> >>  public | <    | geometry      | geometry       | boolean          |
>> >>  public | <#>  | geometry      | geometry       | double precision |
>> >>  public | <->  | geometry      | geometry       | double precision |
>> >>  public | <<   | geometry      | geometry       | boolean          |
>> >>  public | <<   | raster        | raster         | boolean          |
>> >>  public | <<|  | geometry      | geometry       | boolean          |
>> >>  public | <<|  | raster        | raster         | boolean          |
>> >>
>> >> And this Pgis doc page describes them:
>> >> http://postgis.net/docs/manual-2.0/reference.html#Operators
>> >>
>> >> All st_* predicates internally use GIST operator.
>> >>
>> >> Nicolas
>> >>
>> >>
>> >> On 22 January 2013 16:39, Pietro Rossin <pierigis at gmail.com> wrote:
>> >>
>> >>> Is there any reference list for the GIST operators?
>> >>> Thnx
>> >>> Pietro
>> >>>
>> >>>
>> >>> 2013/1/22 Pietro Rossin <pierigis at gmail.com>
>> >>>
>> >>>> Superthank you!
>> >>>> It works and very fast!
>> >>>> Bie
>> >>>> Pietro
>> >>>>
>> >>>>
>> >>>> 2013/1/22 Nicolas Ribot-2 [via PostGIS] <[hidden email]<
>> http://user/SendEmail.jtp?type=node&node=5002380&i=0>
>> >>>> >
>> >>>>
>> >>>>> Hi,
>> >>>>>
>> >>>>> GIST index is only used with GIST operators:
>> >>>>> &&, st_intersects, st_touches, etc.
>> >>>>>
>> >>>>> You should rewrite your query to define a geographic bounding box
>> and
>> >>>>> test your data against it:
>> >>>>>
>> >>>>> SELECT the_geom, layer, id
>> >>>>>   FROM gauss_b.polilineegb
>> >>>>>   where the_geom && 'BOX(2365385 5075215, 2366019 5075752)'::box2d;
>> >>>>>
>> >>>>> Nicolas
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>> On 22 January 2013 15:38, Pietro Rossin <[hidden email]<
>> http://user/SendEmail.jtp?type=node&node=5002378&i=0>
>> >>>>> > wrote:
>> >>>>>
>> >>>>>> Hello
>> >>>>>> I have a table with about 6.000.000 polilines records.
>> >>>>>> The geometry value is in the_geom column.
>> >>>>>> I made an index with this instruction
>> >>>>>>
>> >>>>>> *************
>> >>>>>> CREATE INDEX idx_polilineegb_geom
>> >>>>>>   ON gauss_b.polilineegb
>> >>>>>>   USING gist
>> >>>>>>   (the_geom );
>> >>>>>> *************
>> >>>>>>
>> >>>>>> If I try a query like this:
>> >>>>>> **********************
>> >>>>>> SELECT the_geom, layer, id
>> >>>>>>   FROM gauss_b.polilineegb
>> >>>>>>   where
>> >>>>>>   (st_xmin(the_geom)=2365385 and st_ymin(the_geom)=5075215) and
>> >>>>>> (st_xmax(the_geom)=2366019 and st_ymax(the_geom)=5075752);
>> >>>>>> **********************
>> >>>>>>
>> >>>>>> It takes ages to execute..
>> >>>>>> I take a look at the explain query it says:
>> >>>>>>
>> >>>>>> ************************
>> >>>>>> "Seq Scan on polilineegb  (cost=0.00..727911.08 rows=1 width=2924)"
>> >>>>>> "  Filter: ((st_xmin((the_geom)::box3d) = 2365385::double
>> precision)
>> >>>>>> AND
>> >>>>>> (st_ymin((the_geom)::box3d) = 5075215::double precision) AND
>> >>>>>> (st_xmax((the_geom)::box3d) = 2366019::double precision) AND
>> >>>>>> (st_ymax((the_geom)::box3d) = 5075752::double precision))"
>> >>>>>> ************************
>> >>>>>> This is a Seq Scan, so it doesn't use the index, right?
>> >>>>>> Why?
>> >>>>>> Is there some more performing index to be used??
>> >>>>>>
>> >>>>>> Thank you
>> >>>>>> Pietro
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>> --
>> >>>>>> View this message in context:
>> >>>>>>
>> http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html
>> >>>>>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>> >>>>>> _______________________________________________
>> >>>>>> postgis-users mailing list
>> >>>>>> [hidden email] <
>> http://user/SendEmail.jtp?type=node&node=5002378&i=1>
>> >>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >>>>>>
>> >>>>>
>> >>>>>
>> >>>>> _______________________________________________
>> >>>>> postgis-users mailing list
>> >>>>> [hidden email] <
>> http://user/SendEmail.jtp?type=node&node=5002378&i=2>
>> >>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >>>>>
>> >>>>>
>> >>>>> ------------------------------
>> >>>>>  If you reply to this email, your message will be added to the
>> >>>>> discussion below:
>> >>>>>
>> >>>>>
>> http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html
>> >>>>>  To unsubscribe from Why postgis doesn't use index during query?,
>> click
>> >>>>> here.
>> >>>>> NAML<
>> http://postgis.17.n6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
>> >
>> >>>>>
>> >>>>
>> >>>>
>> >>>> ------------------------------
>> >>>> View this message in context: Re: Why postgis doesn't use index
>> during
>> >>>> query?<
>> http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html
>> >
>> >>>>
>> >>>> Sent from the PostGIS - User mailing list archive<
>> http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html>at Nabble.com.
>> >>>>
>> >>>> _______________________________________________
>> >>>> postgis-users mailing list
>> >>>> postgis-users at lists.osgeo.org
>> >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >>>>
>> >>>>
>> >>>
>> >>> _______________________________________________
>> >>> postgis-users mailing list
>> >>> postgis-users at lists.osgeo.org
>> >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >>>
>> >>>
>> >>
>> >
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at lists.osgeo.org
>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >
>> >
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> 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/20130122/0dca0061/attachment.html>

More information about the postgis-users mailing list