[postgis-users] Why postgis doesn't use index during query?
Carsten Hogertz
carsten.hogertz at gmail.com
Tue Jan 22 09:07:16 PST 2013
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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130122/f1a1b6e1/attachment.html>
More information about the postgis-users
mailing list