Re: Is this planner choice easily explained?

At 10:38 AM 22/11/2002 -0500, Tom Lane wrote:
>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...).

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.

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

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.

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

Indeed; I think this is exactly what we are seeing.

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