[postgis-users] Why postgis doesn't use index during query?
Francois Hugues
hugues.francois at irstea.fr
Tue Jan 22 08:58:39 PST 2013
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
>
>
More information about the postgis-users
mailing list