[postgis-users] Why postgis doesn't use index during query?
Francois Hugues
hugues.francois at irstea.fr
Tue Jan 22 09:21:06 PST 2013
You can call me Hugues ;)
I simply would have try select max(dist_min) from dist_min after your with.
If this too long too, you will have to estimate X value : no too small or some polygons may miss you, and not too big or you will not improve perfs. Depending on your question but this may be common sense too (if you are pedestrian you may not want to walk for ten kilometers to find your hostel) or may rely on the spatial density of your polygons.
Hope it will help you !
Hugues.
________________________________
De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Carsten Hogertz
Envoyé : mardi 22 janvier 2013 18:07
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Why postgis doesn't use index during query?
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
More information about the postgis-users
mailing list