From: | Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Matteo Beccati <php(at)beccati(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: -HEAD planner issue wrt hash_joins on dbt3 ? |
Date: | 2006-09-27 05:58:39 |
Message-ID: | 451A130F.7030400@kaltenbrunner.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
>> Tom Lane wrote:
>>> It evidently thinks that most of the rows in the join of part and
>>> partsupp won't have any matching rows in lineitem, whereas on average
>>> there are about 7 matching rows apiece. So that's totally wacko, and
>>> it's not immediately obvious why. Could we see the pg_stats entries for
>>> part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey,
>>> lineitem.l_partkey, lineitem.l_suppkey?
>
>> http://www.kaltenbrunner.cc/files/dbt3_beta1_statistics.txt
>
> OK, so we have 2 million parts and 100000 suppliers, and ANALYZE doesn't
> seem to have been too far off at estimating either of those numbers.
> I think the problem is that there are not very many suppliers for any
> particular part, and thus the condition "part match AND supplier match"
> is really not much more selective than "part match" alone. The planner
> is supposing that their selectivities are independent, which they
> aren't.
looks like there are exactly 4 suppliers for any given part so that
seems indeed like the problem :-(
>
> Offhand I don't see any good way to fix this without multi-column
> statistics, which is something that's certainly not happening for 8.2 :-(
too bad - however any idea on one of the other troubling querys (q21) I
mentioned in the mail I resent to the list (after the original one got
lost)?
http://archives.postgresql.org/pgsql-hackers/2006-09/msg02011.php
Stefan
From | Date | Subject | |
---|---|---|---|
Next Message | Zdenek Kotala | 2006-09-27 07:31:28 | Re: horo(r)logy test fail on solaris (again and solved) |
Previous Message | Jim C. Nasby | 2006-09-27 05:52:53 | Re: Block B-Tree concept |