Re: Postgres picks suboptimal index after building of an extended statistics

On 8/11/21 2:48 AM, Peter Geoghegan wrote:
> On Wed, Jun 23, 2021 at 7:19 AM Andrey V. Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> Ivan Frolkov reported a problem with choosing a non-optimal index during
>> a query optimization. This problem appeared after building of an
>> extended statistics.
> 
> Any thoughts on this, Tomas?
> 

Thanks for reminding me, I missed / forgot about this thread.

I agree the current behavior is unfortunate, but I'm not convinced the 
proposed patch is fixing the right place - doesn't this mean the index 
costing won't match the row estimates displayed by EXPLAIN?

I wonder if we should teach clauselist_selectivity about UNIQUE indexes, 
and improve the cardinality estimates directly, not just costing for 
index scans.

Also, is it correct that the patch calculates num_sa_scans only when 
(numIndexTuples >= 0.0)?


regards

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