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

From: "Andrey V(dot) Lepikhov" <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Postgres picks suboptimal index after building of an extended statistics
Date: 2021-08-13 07:01:24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 8/12/21 4:26 AM, 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 think, it is not a problem. In EXPLAIN you will see only 1 row
with/without this patch.
> I wonder if we should teach clauselist_selectivity about UNIQUE indexes,
> and improve the cardinality estimates directly, not just costing for
> index scans.
This idea looks better. I will try to implement it.
> Also, is it correct that the patch calculates num_sa_scans only when
> (numIndexTuples >= 0.0)?
Thanks, fixed.

Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
v2-0001-In-the-case-of-an-unique-one-row-btree-index-scan-on.patch text/x-patch 5.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Gavin Flower 2021-08-13 07:33:25 Re: Default to TIMESTAMP WITH TIME ZONE?
Previous Message Greg Nancarrow 2021-08-13 06:52:31 Re: Parallel scan with SubTransGetTopmostTransaction assert coredump