Message-ID: <15381eea-cbc3-4087-9d90-ab752292bd54@postgrespro.ru>
Date: Mon, 4 Mar 2024 11:57:56 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: a wrong index choose when statistics is out of date
Content-Language: en-US
To: Andy Fan <zhihuifan1213@163.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <878r2zeqsp.fsf@163.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <878r2zeqsp.fsf@163.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/15381eea-cbc3-4087-9d90-ab752292bd54%40postgrespro.ru>
Precedence: bulk

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.
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.

-- 
regards,
Andrei Lepikhov
Postgres Professional