Re: Bad estimate with partial index

On 4/19/22 23:08, Tom Lane wrote:
> I wrote:
>> it looks like the problem is that the extended stats haven't been used
>> while forming the estimate of the number of index entries retrieved, so
>> we overestimate the cost of using this index.
>> That seems like a bug.  Tomas?
> 
> I dug into this enough to locate the source of the problem.
> btcostestimate includes the partial index clauses in what it
> sends to clauselist_selectivity, but per the comments for
> add_predicate_to_index_quals:
> 
>  * Note that indexQuals contains RestrictInfo nodes while the indpred
>  * does not, so the output list will be mixed.  This is OK for both
>  * predicate_implied_by() and clauselist_selectivity(), but might be
>  * problematic if the result were passed to other things.
> 
> That comment was true when it was written, but it's been falsified
> by the extended-stats patches, which have added a whole lot of logic
> in and under clauselist_selectivity that ignores clauses that are not
> RestrictInfos.
> 
> While we could perhaps fix this by having add_predicate_to_index_quals
> add RestrictInfos, I'm inclined to feel that the extended-stats code
> is in the wrong.  The contract for clauselist_selectivity has always
> been that it could optimize if given RestrictInfos rather than bare
> clauses, not that it would fail to work entirely without them.
> There are probably more places besides add_predicate_to_index_quals
> that are relying on that.
> 

Yes, that seems like a fair assessment. I'll look into fixing this, not
sure how invasive it will get, though.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company