References: <878r2zeqsp.fsf@163.com>
 <15381eea-cbc3-4087-9d90-ab752292bd54@postgrespro.ru>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 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
In-reply-to: <15381eea-cbc3-4087-9d90-ab752292bd54@postgrespro.ru>
Message-ID: <87msrecjel.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87msrecjel.fsf%40163.com>
Precedence: bulk


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