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(dot)lepikhov(at)postgrespro(dot)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