The issue can be reproduced with the following steps:
create table x_events (.., created_at timestamp, a int, b int);
create index idx_1 on t(created_at, a);
create index idx_2 on t(created_at, b);
query:
select * from t where create_at = current_timestamp and b = 1;
index (created_at, a) rather than (created_at, b) may be chosen for the
above query if the statistics think "create_at = current_timestamp" has
no rows, then both index are OK, actually it is true just because
statistics is out of date.
I just run into this again recently and have two new idea this time,
I'd like gather some feedback on this.
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.
This is different from letting user using a PreparedStmt directly
because it is possible that we always choose a custom plan, the
easiest way to make this happen is we do a planning time partition
prune.
2. Use some AI approach to forecast the data it doesn't gather yet. The
training stage may happen at analyze stage, take the above case for
example, it may get a model like 'there are 100 rows per second for
the time during 9:00 to 18:00 and there are 2 rows per seconds for
other time range.
For now, I think option 1 may be easier to happen.
--
Best Regards
Andy Fan
