Re: Very poor estimates from planner

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)