Well, I've managed to track down the cause of improper plans.
Due to the data distribution n_distinct had been estimated way too low.
I've manually set it to be 195300 instead of 15500 (with stats_target=200):
select tablename,attname,null_frac,avg_width,n_distinct,correlation
from pg_stats
where (tablename,attname) IN
(VALUES ('meta_version','account_id'),('account','customer_id'));
tablename | attname | null_frac | avg_width | n_distinct | correlation
--------------+-------------+-----------+-----------+------------+-------------
account | customer_id | 0 | 4 | 57 | 0.998553
meta_version | account_id | 0 | 4 | 195300 | 0.0262315
(2 rows)
Still, optimizer underestimates rows returned by the IndexScan heavily:
http://explain.depesz.com/s/pDw
Is it possible to get correct estimates for the IndexScan on the right side
of the NestedLoops? I assume estimation is done by the B-tree AM and
it is seems to be not affected by the STATISTICS parameter of the
column.
2012/9/29 Виктор Егоров <vyegorov@gmail.com>:
> Now I have the following plan:
> http://explain.depesz.com/s/YZJ
>
> Second query takes twice more time.
--
Victor Y. Yegorov
