Re: Very poor estimates from planner

Rod Taylor <rbt(at)rbt(dot)ca> writes:
> Effectively, the planner has amazingly inaccurate row estimates.

It seems the key estimation failure is in this join step:

-> Hash Join (cost=1230.79..60581.82 rows=158 width=54) (actual time=1262.35..151200.29 rows=1121988 loops=1)
Hash Cond: ("outer".account_id = "inner".account_id)
-> Hash Join (cost=1226.78..52863.43 rows=1542558 width=50) (actual time=1261.63..100418.30 rows=1573190 loops=1)
(join of bsod, tsb, tss)
-> Hash (cost=4.01..4.01 rows=1 width=4) (actual time=0.33..0.33 rows=0 loops=1)
-> Index Scan using single_null_parent_account_hack on account ap (cost=0.00..4.01 rows=1 width=4) (actual time=0.26..0.28 rows=1 loops=1)
Filter: (parent_account_id IS NULL)

The estimated number of rows out of the join of bsod, tsb, tss isn't far
off, but the estimate for the result of joining that to ap is WAY off.
Apparently the planner thinks that only a few rows in the join will have
matches in ap, but really they almost all do. Any idea why? The
account_id stats for each seem to be the thing to look at.

> Any hints? I'm basically stuck. Oh, and I would like to ask for a
> pgadmin feature -- visual explain :)

You do know that Red Hat has been offering a Visual Explain tool for
some time?
http://sources.redhat.com/rhdb/
I've not had much occasion to use it myself, but it works ...

regards, tom lane