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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Postgres picks suboptimal index after building of an extended statistics
Date: 2022-07-11 07:57:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 7/8/22 03:07, Tom Lane wrote:
> Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> writes:
>> On 12/8/21 04:26, Tomas Vondra wrote:
>>> I wonder if we should teach clauselist_selectivity about UNIQUE indexes,
>>> and improve the cardinality estimates directly, not just costing for
>>> index scans.
>> I tried to implement this in different ways. But it causes additional
>> overhead and code complexity - analyzing a list of indexes and match
>> clauses of each index with input clauses in each selectivity estimation.
>> I don't like that way and propose a new patch in attachment.
> I looked at this briefly. I do not think that messing with
> btcostestimate/genericcostestimate is the right response at all.
> The problem can be demonstrated with no index whatever, as in the
> attached shortened version of the original example. I get

I partly agree with you. Yes, I see the problem too. But also we have a
problem that I described above: optimizer don't choose a path with
minimal selectivity from a set selectivities which shows cardinality
less than 1 (see badestimate2.sql).
New patch (see in attachment), fixes this problem.

Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Use-Index-path-with-the-best-selectivity-estimation.patch text/x-patch 1.2 KB
badestimate2.sql application/sql 635 bytes

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Matthias van de Meent 2022-07-11 08:47:53 Re: Reducing Memory Consumption (aset and generation)
Previous Message Thomas Munro 2022-07-11 07:50:16 Re: AIX support - alignment issues