"Julian Scarfe" <julian@xxxxxxxxxxx> 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@xxxxxxxxxxxxx>
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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
