Re: Performance on multiple OR conditions inside ()

Hi, so one solution is to use the most common feature first (), in this case
the houses ?

|What version of PostGIS are you using?

1.5.1

2011/1/13 Nicklas Avén <nicklas(dot)aven(at)jordogskog(dot)no>

> Hallo Håvard
>
> The planner is supposed to take care of that. It estimates ehat is the
> cheapest part of the OR statements and checks that. If true, then
> nothing is done with the others.
>
> Do you have spatial indexes on the geometry columns?
>
> Do you know if they are used by the planner?
>
> Even if the indexes is in place and are used this query will probably be
> slow because of how the spatial index works.
>
> What the spatial index does for ST_Within and ST_DWithin is to tell if
> the geometries has overlapping bounding boxes (or expanded bounding
> boxes in ST_DWithin case). If they do the index is of no more help and
> the rest of the calculation has to be done vertex by vertex which is
> costly.
>
> So, the problem is when the bounding boxes covers many geoemtries, then
> the part of the work that the index can help with is small.
>
> There is techniques to slice the big geometry in smaller pieces, build a
> new index and things will go faster.
>
> Long roads often have this problem. If you want to find all houses along
> a road the bounding box test will find many more houses than those close
> to the road (If the road is not going just north/south or east/west)
>
> I don't think it should do any difference for the planner but I would
> test to build the query with joins instead.
>
> What version of PostGIS are you using?
>

>
>
> Regards
>
> Nicklas Avén
>
>
> On Thu, 2011-01-13 at 17:47 +0100, Håvard Wahl Kongsgård wrote:
> > Hi, I have a spatial query with multiple OR statements, for large
> > tables it's very slow. Is it possible to skip the spatial lookup on
> > the other conditions if first(previous) condition equal 1, and thereby
> > increase the performance?
> >
> > SELECT vciia_main.sitrp,vciia_main.date_time from vciia_main,
> > south_vietnam72, roads, rails, houses, city where
> > st_within(vciia_main.geom, south_vietnam72.geom) and date_time is not
> > null and (st_dwithin(vciia_main.geom, roads.geom, 500) OR
> > st_dwithin(vciia_main.geom, rails.geom, 500) or
> > st_dwithin(vciia_main.geom, city.geom, 800) or
> > st_dwithin(vciia_main.geom, houses.the_geom, 500))
> >
> > --
> > Håvard Wahl Kongsgård
> > Peace Research Institute Oslo (PRIO)
> >
> > http://havard.security-review.net/
> >
>
>
>

--
Håvard Wahl Kongsgård
Peace Research Institute Oslo (PRIO)

http://havard.security-review.net/