From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [Fwd: Re: Very poor estimates from planner] |
Date: | 2003-11-06 19:34:23 |
Message-ID: | 1068147262.64262.163.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, 2003-11-06 at 10:35, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)ca> writes:
> >> -> Hash Join (cost=3D1230.79..60581.82 rows=3D158 width=3D54)=
> > (actual time=3D1262.35..151200.29 rows=3D1121988 loops=3D1)
> >> Hash Cond: ("outer".account_id =3D "inner".account_id)
> >> -> Hash Join (cost=3D1226.78..52863.43 rows=3D1542558 w=
> > idth=3D50) (actual time=3D1261.63..100418.30 rows=3D1573190 loops=3D1)
> >> (join of bsod, tsb, tss)
>
> (btw, would you mind turning off MIME encoding in your mails to the PG
> lists? It's a real PITA to quote.)
I can, though I would ask which email client you use that doesn't pull
content out of mime encoded emails.
> > So yes, since this is a full table scan all values will be joined since
> > the foreign key enforces them all to exist.
>
> Well, no, because only 1121988 rows come out of the join when 1573190
> went in. So the actual selectivity of the join is about 70%. The
> question is why the planner is estimating the selectivity at 0.01%
> (158/1542558).
>
> Could we see the pg_stats rows for service.account_id and
> account.account_id?
relname | attname | stanullfrac | stawidth | stadistinct | stakind1
| stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4
|
stanumbers1 | stanumbers2 |
stanumbers3 | stanumbers4 |
stavalues1 | stavalues2 | stavalues3 |
stavalues4
---------+------------+-------------+----------+-------------+----------+----------+----------+----------+--------+--------+--------+--------+--------------------------------------------------------------------------------------------------+-------------+-------------+-------------+-----------------------------------------------------------------+------------+------------+------------
service | account_id | 0 | 4 | 10 | 1
| 3 | 0 | 0 | 96 | 97 | 0 | 0 |
{0.388393,0.0825893,0.078125,0.0758929,0.0703125,0.0647321,0.0647321,0.0636161,0.0625,0.0491071} | {0.591672} | | | {1,8221,8223,8226,8222,8218,8220,8219,8224,8225} | | |
account | account_id | 0 | 4 | -1 | 2
| 3 | 0 | 0 | 97 | 97 | 0 | 0
| | {0.97034} | | | {1,10178,12136,14099,16054,18011,19966,21924,23881,26018,27995} | | |
(2 rows)
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-11-06 19:34:56 | Re: Changes to Contributor List |
Previous Message | Peter Eisentraut | 2003-11-06 19:30:28 | Re: Changes to Contributor List |