В предыдущем посте я рассматривал решения, предложенные @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 была проблема с этим.