Re: a wrong index choose when statistics is out of date

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