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

From: Andrey 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-30 06:44:28
Message-ID: a5a18d86-c0e5-0ceb-9a18-be1beb2d2944@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 12/8/21 04: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
> 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 rewrote the patch. It's now simpler and shorter. May be more convenient.
Also, it includes a regression test to detect the problem in future.
>
> 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.

--
regards,
Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Estimating-number-of-fetched-rows-in-a-btree-index-w.patch text/plain 5.9 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2021-08-30 07:02:15 Re: list of acknowledgments for PG14
Previous Message Amit Kapila 2021-08-30 06:44:09 Re: Separate out FileSet from SharedFileSet (was Re: pgsql: pgstat: Bring up pgstat in BaseInit() to fix uninitialized use o)