Re: Index use with left join

"Julian Scarfe" <julian(at)avbrief(dot)com> writes:
> Does the planner "realise" that
> the intersection, Query 6, will still return 150 rows, or does it assume
> independence of the filters in some way and estimate
> 20,000*(150/20,000)*(396/20,000)?

It assumes independence of the conditions --- which is why having two
of them reduced the rowcount estimate so much. There are some limited
cases in which it can recognize redundant conditions, but offhand I
think that only works for scalar inequalities (like "x < 5 AND x < 6").
There just hasn't been a lot of work on geometric conditions ...

> Any general suggestions for workarounds?

Not much, other than trying to avoid redundant conditions.

Did you look into the state of the PostGIS work on geometric statistics?
It's bad enough not knowing how to combine geometric conditions, but
when the individual estimates themselves are completely bogus, you've
really got a problem :-(

regards, tom lane