At 10:38 AM 22/11/2002 -0500, Tom Lane wrote:
I am not at all sure what their mindset is -- I may have just misunderstood their responses. Very odd. Up until now I thought I held the record for mule-headedness on this sort of thing.If they don't believe that, they are wrong (and pretty muleheaded, to continue disbelieving it in the face of indisputable evidence to the contrary...).
Based on my own database with about 30GB of mail, the planner is easily capable of coming up with the right strategy here -- so long as an analyze is done. I have managed to construct trivial examples that exactly mirror the behaviour above, but which work properly after an analyze.This bet is evidently wrong, but it's hard to tell whether it's wrong because no statistics are available, or because the system isn't making the right deductions from the stats it has, or because the stats aren't adequate to model the situation.
Indeed; I think this is exactly what we are seeing.(For example, we currently do not have any cross-column correlation stats. If message_idnr and messageblk_idnr are strongly correlated, which I'm suspecting is likely, the rows with message_idnr = 100 would not be randomly scattered in the messageblk_idnr index --- but the system is assuming they will be in order to estimate how long it will take to find the first one.)
Thanks very much for the insights; I have relayed this to the other list -- I'll let you know if anything unexpected happens.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
