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

Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)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