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