Re: Optimizer misses big in 10.4 with BRIN index

On 07/26/2018 10:11 AM, Emre Hasegeli wrote:
>> Isn't the 23040 just the totalpages * 10 per `return totalpages * 10;`
>> in bringetbitmap()?
> 
> Yes, it is just confusing.  The correct value is on one level up of
> the tree.  It is 204 + 4404 rows removed by index recheck = 4608, so
> the estimate is not only 150x but 733x off :(.
> 
> The sequential scan plan shows 204 + 1125498 rows removed by filter =
> 1125702 as the actual table size.  However the former plan estimates
> to get 3377106 rows from the index.  That is 3x of the table size.
> The selectivity estimation cannot be greater than 1.  If I am not
> missing anything, the general statistics of this table should be
> seriously outdated.
> 

Hmmm, yeah. It's s bot confusing, and the parallel plan does not improve 
the situation either :-(

Arcadiy, can you provide plans with parallel query disabled? Or even 
better, produce a test case that reproduces this (using synthetic data, 
anonymized data or something like that, if needed).


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services