Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16226

TRX_ID-based versioned tables performance improvement

    XMLWordPrintable

    Details

      Description

      Current performance of SELECT by timestamp from TRX_ID-based tables is very low due to linear scan of TRANSACTION_REGISTRY table when doing timestamp -> TRX_ID translation. Utilize join optimizer for querying TRANSACTION_REGISTRY:

      1. For each timestamped Vers_history_point selector forge TRANSACTION_REGISTRY subquery and add it to SELECT.

      Example

      Query

      select *, row_start, row_end from i1 for system_time as of timestamp @t1;
      

      is transformed into

      select i1.x as x,
             i1.row_start as row_start,
             i1.row_end as row_end
      from i1
      for SYSTEM_TIME all
      join (
         select transaction_id
         from mysql.transaction_registry
         where commit_timestamp <= @t1
         order by commit_timestamp desc
         limit 1
      ) __trt_0
      where trt_trx_sees(i1.row_end, __trt_0.transaction_id)
        and trt_trx_sees_eq(__trt_0.transaction_id, i1.row_start)
      

      2. subquery must be excluded from wildcard expansion, i.e.

      select transaction_id from i1 for system_time as of timestamp @t1;
      

      must be resolved to i1.transaction_id.

      Detailed analysis and work progress

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: