Re: NestedLoops over BitmapScan question

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