Re: Index use with left join

> "Julian Scarfe" <julian@avbrief.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)?

From: "Tom Lane" <tgl@sss.pgh.pa.us>

> 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").

Even that's smarter than I dared hope for!

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

No, though PostGIS is clearly the way forward for my needs in the
medium/long term.

PostGIS stores bounding boxes for its geometric features.  The operators
like && and @ work as intersect and containment for the bounding boxes,
while Intersects() and Contains() use more exact but presumably
computationally expensive functions.  I don't yet know how these, GiST
indexes and the planner get along together.  But I imagine the issue I've
come across is one of the, if not the, most important one in spatially
enabled databases.

Thanks again

Julian