Skip site navigation (1) Skip section navigation (2)

Re: Is this planner choice easily explained?

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Is this planner choice easily explained?
Date: 2002-11-23 02:53:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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

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://          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from   |/

In response to

pgsql-bugs by date

Next:From: andrzejDate: 2002-11-23 08:27:43
Subject: Re: like not using indexes in 7.3b5?
Previous:From: Stefanos HarhalakisDate: 2002-11-22 20:51:16
Subject: vacumm error

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group