Свободное индексное сканирование ака Skip Scan в PostgreSQL 🐘🚀🚀

В предыдущем посте я рассматривал решения, предложенные @ryanbooz в его статье Select the Most Recent Record (of Many Items) With PostgreSQL , и как они применимы к YugabyteDB. Райан работает с Timescale, который реализует Index Skip Scan прозрачно для DISTINCT ON (truck_id). Он упоминает альтернативу PostgreSQL с рекурсивным CTE, документированным как Loose indexscan, но также говорит, что самым большим недостатком этого подхода является то, что гораздо сложнее вернуть несколько столбцов данных. Здесь показан DISTINCT, возвращающий только один столбец.

Вот моя версия, работающая с дополнительными столбцами, для самого быстрого ответа на вопрос о самой последней записи (из многих элементов), когда у вас есть правильный индекс.

Я создаю те же таблицы, что и в предыдущем сообщении (и в статье Райана Буза):

drop table truck_readings;

create table truck_readings (
 truck_id bigint 
 ,ts timestamptz
 ,milage int
 ,primary key(truck_id, ts)
);
Вход в полноэкранный режим Выход из полноэкранного режима

Здесь я использую YugabyteDB, распределенную базу данных SQL с открытым исходным кодом, совместимую с PostgreSQL. Синтаксис тот же, но строки распределяются, по умолчанию, по хэшу на первом столбце.

Я вставляю несколько строк: десять тысяч показаний для каждого из двух тысяч грузовиков.

with trucks as (select generate_series(1,2000) truck_id)
insert into truck_readings(truck_id, ts, milage)
select truck_id, now() - 
 interval '1 second' * generate_series(1,10000)
 , 42 from trucks;
analyze truck_readings;
Войти в полноэкранный режим Выход из полноэкранного режима

На этом этапе, чтобы получить последние показания для каждого грузовика, у нас есть те же решения, что и в предыдущем посте. Список грузовиков помогает, а сохранение последнего значения с помощью триггера является одним из эффективных решений.

В этом посте я использую технику, похожую на сканирование с пропуском индекса, но управляемую из SQL предложением WITH RECURSIVE. В PostgreSQL, который не является распределенным, или в YugabyteDB при шардинге по диапазону, вам не нужен еще один индекс. Но если вы используете хэш-шаринг, или просто когда у вас есть сгенерированный первичный ключ, вам нужен вторичный индекс. Вот он:

create index truck_last_reading on truck_readings 
( truck_id asc, ts asc) include (milage);
Войти в полноэкранный режим Выход из полноэкранного режима

Этот индекс упорядочен сначала по truck_id, а затем по ts, временной метке. Он включает все нужные мне столбцы. Здесь мне нужен пробег для последнего показания каждого грузовика.

Моя цель — начать с конца индекса, чтобы у меня было последнее показание для последнего грузовика:

yugabyte=#

  select
   last_truck_last_reading.truck_id,
   last_truck_last_reading.milage
   from truck_readings last_truck_last_reading
  order by
   last_truck_last_reading.truck_id desc,
   last_truck_last_reading.ts desc
  limit 1;

 truck_id | milage
----------+--------
     2000 |     42
(1 row)
Войти в полноэкранный режим Выйти из полноэкранного режима

Затем перейти к следующему:

yugabyte=#

  select truck_id,milage from truck_readings
  where truck_id < 2000
  order by truck_id desc, ts desc limit 1;

 truck_id | milage
----------+--------
     1999 |     42
Войти в полноэкранный режим Выйти из полноэкранного режима

Я могу объединить их с помощью join, чтобы получить truck_id из предыдущего:

with truck_last_reading as (
  select
   last_truck_last_reading.truck_id,
   last_truck_last_reading.milage
   from truck_readings last_truck_last_reading
  order by
   last_truck_last_reading.truck_id desc,
   last_truck_last_reading.ts desc
  limit 1
)
 select
  next_truck_last_reading.truck_id,
  next_truck_last_reading.milage
 from truck_last_reading, lateral
 (
  select truck_id,milage from truck_readings
  where truck_id < truck_last_reading.truck_id
  order by truck_id desc, ts desc limit 1
 )
 as next_truck_last_reading;
Войти в полноэкранный режим Выйти из полноэкранного режима

С РЕКУРСИВНЫМ … LATERAL

Наконец, я могу показать обе строки с помощью UNION ALL. И сделать это рекурсивно, чтобы найти следующий truck_id и присоединить его к результату:

with recursive truck_last_reading as (
 (
  select
   last_truck_last_reading.truck_id,
   last_truck_last_reading.milage
   from truck_readings last_truck_last_reading
  order by
   last_truck_last_reading.truck_id desc,
   last_truck_last_reading.ts desc
  limit 1
 )
union all
 select
  next_truck_last_reading.truck_id,
  next_truck_last_reading.milage
 from truck_last_reading, lateral
 (
  select truck_id,milage from truck_readings
  where truck_id < truck_last_reading.truck_id
  order by truck_id desc, ts desc limit 1
 )
 as next_truck_last_reading
) select * from truck_last_reading
order by truck_id,milage;
Войти в полноэкранный режим Выйти из полноэкранного режима

Это окончательный запрос, который дает требуемый результат (последнее чтение, в порядке ts, для каждого truck_id и возвращает milage). План выполнения оптимален:
,

                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual time=1078.569..1078.661 rows=2000 loops=1)
   Output: truck_last_reading.truck_id, truck_last_reading.milage
   Sort Key: truck_last_reading.truck_id, truck_last_reading.milage
   Sort Method: quicksort  Memory: 142kB
   CTE truck_last_reading
     ->  Recursive Union (actual time=0.872..1075.442 rows=2000 loops=1)
           ->  Subquery Scan on "*SELECT* 1" (actual time=0.871..0.872 rows=1 loops=1)
                 Output: "*SELECT* 1".truck_id, "*SELECT* 1".milage
                 ->  Limit (actual time=0.871..0.871 rows=1 loops=1)
                       Output: last_truck_last_reading.truck_id, last_truck_last_reading.milage, last_truck_last_reading.ts
                       ->  Index Only Scan Backward using truck_last_reading on public.truck_readings last_truck_last_reading (actual time=0.870..0.870 rows=1 loops=1)
                             Output: last_truck_last_reading.truck_id, last_truck_last_reading.milage, last_truck_last_reading.ts
                             Heap Fetches: 0
           ->  Nested Loop (actual time=0.525..0.526 rows=1 loops=2000)
                 Output: truck_readings.truck_id, truck_readings.milage
                 ->  WorkTable Scan on truck_last_reading truck_last_reading_1 (actual time=0.000..0.000 rows=1 loops=2000)
                       Output: truck_last_reading_1.truck_id, truck_last_reading_1.milage
                 ->  Limit (actual time=0.524..0.525 rows=1 loops=2000)
                       Output: truck_readings.truck_id, truck_readings.milage, truck_readings.ts
                       ->  Index Only Scan Backward using truck_last_reading on public.truck_readings (actual time=0.513..0.513 rows=1 loops=2000)
                             Output: truck_readings.truck_id, truck_readings.milage, truck_readings.ts
                             Index Cond: (truck_readings.truck_id < truck_last_reading_1.truck_id)
                             Heap Fetches: 0
   ->  CTE Scan on truck_last_reading (actual time=0.874..1077.252 rows=2000 loops=1)
         Output: truck_last_reading.truck_id, truck_last_reading.milage
 Planning Time: 0.165 ms
 Execution Time: 1078.821 ms
 Peak Memory Usage: 337 kB
(28 rows)
Войти в полноэкранный режим Выход из полноэкранного режима

Память рабочей области не требуется, так как за каждую итерацию считывается только одна строка. При этом индекс используется наилучшим образом: одно Index Only Scan для каждого truck_id, и без необходимости сначала получать список truck_id. Каждое чтение из индекса обращается непосредственно к нужному месту и считывает только одну строку. YugabyteDB накладывает LIMIT 1 на сервер планшетного хранилища.

Теоретически такой доступ должен быть возможен при хэш-шаринге по truck_id и сортировке по хэшу, а не по значению. Цель — просто добраться до следующего truck_id, тогда order by yb_hash_code(truck_id) desc, truck_id desc, ts desc мог бы использовать тот же бит, но для этого нет реализации. Пожалуйста, откройте git issue, если вам это нужно. Но вы также можете объявить таблицу с первичным ключом (truck_id desc, ts desc) и предварительно разделить ее или позволить авторазделению сделать это при росте таблицы. Обратите внимание, что я проверил этот пример на версии 2.15.1, потому что в 2.15.0 была проблема с этим.

Оцените статью
devanswers.ru
Добавить комментарий