[Fwd: Re: Very poor estimates from planner] - Mailing list pgsql-hackers

On Thu, 2003-11-06 at 10:35, Tom Lane wrote:
> Rod Taylor <rbt@rbt.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