Re: Performance on multiple OR conditions inside ()

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/
>