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

On 12/8/2021 06:26, Tomas Vondra wrote:
> 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)?
I can't stop thinking about this issue. It is bizarre when Postgres 
chooses a non-unique index if a unique index gives us proof of minimum scan.
I don't see a reason to teach the clauselist_selectivity() routine to 
estimate UNIQUE indexes. We add some cycles, but it will work with btree 
indexes only.
Maybe to change compare_path_costs_fuzzily() and add some heuristic, for 
example:
"If selectivity of both paths gives us no more than 1 row, prefer to use 
a unique index or an index with least selectivity."

-- 
regards,
Andrey Lepikhov
Postgres Professional