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