From: Andy Fan <zhihuifan1213@163.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: a wrong index choose when statistics is out of date
Date: Mon, 04 Mar 2024 19:37:54 +0800
Message-ID: <87msrecjel.fsf@163.com> (raw)
In-Reply-To: <15381eea-cbc3-4087-9d90-ab752292bd54@postgrespro.ru>
References: <878r2zeqsp.fsf@163.com>
<15381eea-cbc3-4087-9d90-ab752292bd54@postgrespro.ru>
Andrei Lepikhov <a.lepikhov@postgrespro.ru> writes:
> On 3/3/2024 14:01, Andy Fan wrote:
>> 1. We can let the user define the column as the value is increased day by
>> day. the syntax may be:
>> ALTER TABLE x_events ALTER COLUMN created_at ALWAYS_INCREASED.
>> then when a query like 'create_at op const', the statistics module
>> can
>> treat it as 'created_at = $1'. so the missing statistics doesn't make
>> difference. Then I think the above issue can be avoided.
> Let me write some words to support your efforts in that way.
> I also have some user cases where they periodically insert data in large
> chunks. These chunks contain 'always increased' values, and it causes
> trouble each time they start an analytic query over this new data before
> the analyze command.
> I have thought about that issue before but invented nothing special
> except a more aggressive analysis of such tables.
I have to say we run into a exactly same sistuation and use the same
trick to solve the problem, and we know no matter how aggressive it is,
the problem may still happen.
> Your trick can work, but it needs a new parameter in pg_type and a lot
> of additional code for such a rare case.
> I'm looking forward to the demo patch.
Maybe my word "auto_increased" is too like a type, but actually what I
want to is adding a new attribute for pg_attribute which ties with one
column in one relation. When we figure out a selective on this
*column*, we do such trick. This probably doesn't need much code.
--
Best Regards
Andy Fan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: zhihuifan1213@163.com, a.lepikhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: a wrong index choose when statistics is out of date
In-Reply-To: <87msrecjel.fsf@163.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox