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

No, the order is not supposed to make any difference. The planner plans
the query from costs and statistics and shouldn't care about the order
(I think)

What you should do is to divide the big geometries and build new index.

That can be done by building a grid with generate_series and cutting the
geometry against that grid by using ST_Intersection between the grid and
your geometry. I have not tried it myself so I don't have any query
written for it.

Another way that might be simplier is to use some other map than taht
south Vietnam map. If you have some map with smaller regions instead the
index will be more efficiency.

Also use ST_Dump to tear apart all geometries as much as possible.

Then build a new index and analyse for the planner to know what you have
done.

One thing to remember is that you in many cases will get the same
geometry many times because it is within the given distance to many of
your regions like ST_Intersects(Mjøsa, fylker) will give many
fylke-mjøsa combinations.

Regards

Nicklas

On Thu, 2011-01-13 at 21:51 +0100, Håvard Wahl Kongsgård wrote:
> 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.aven@jordogskog.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/
>