[MDEV-27288] Add implicit indexes by row_start and row_end for system versioning Created: 2021-12-16  Updated: 2021-12-17

Status: Open
Project: MariaDB Server
Component/s: Versioned Tables
Fix Version/s: None

Type: Task Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Implicit system versioning (with implicit system fields) is almost unusable because versioned queries doe full table scan and there is no way to index them. F.ex. this:

create table t1(x int primary key) with system versioning;
 
explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
drop table t1;

results in

explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    6       Using where
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    6       Using where
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    6       Using where

And when we add indexes:

create table t1(
 
  x int primary key,
 
  row_start timestamp(6) as row start invisible,
 
  row_end timestamp(6) as row end invisible,
 
  index (row_start),
 
  index (row_end),
 
  period for system_time (row_start, row_end)
 
) with system versioning;
 
explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
drop table t1;


explain select * from t1 for system_time as of '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where
 
explain select * from t1 for system_time from '2020-01-01 00:00:00' to '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where
 
explain select * from t1 for system_time between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      range   row_start,row_end       row_start       7       NULL    1       Using index condition; Using where

So adding default indexes by (row_start) and (row_end) actually is a good thing for any system versioned tables. While they are easily added in the second example it is may be not evident to a user, so probably CREATE TABLE should print a warning when no such indexes specified or maybe it should add them implicitly in any case.

 Comments   
Comment by Sergei Golubchik [ 2021-12-16 ]

I'm not sure it's a good idea to create indexes by default. I'd rather have users create them explicitly.

For that I suggested a feature to convert implicit to explicit system versioning. With

ALTER TABLE t1 ADD COLUMN rs TIMESTAMP(6) AS ROW START, ADD COLUMN re TIMESTAMP(6) AS ROW END, ADD PERIOD FOR SYSTEM_TIME(rs,re);

we could make it to convert the versioning to explicit, while preserving all historical information. Columns can be declared invisible too, that's as the user want.

Comment by Aleksey Midenkov [ 2021-12-16 ]

What is the sence in implicit versioning then? The original sence was to make life easier, but now it turns out that implicit versioning is usable only for tests and study examples.

Comment by Sergei Golubchik [ 2021-12-17 ]

The assumption is that users who use system versioning a lot also can use the explicit standard syntax.

And that the implicit syntax is for users who want to track the history of the table but otherwise don't want to change the existing application, they rarely query history and more concerned about having as little performance degradation as possible.

Generated at Thu Feb 08 09:51:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.