Разбор реального SQL-запроса с оконной функцией lead() на большой таблице в PostgreSQL. В статье показано, как читать EXPLAIN ANALYZE, почему запрос уходит в тяжёлую сортировку на диск, какие узкие места возникают и какие практические выводы из этого можно сделать для аналитических задач.

Последние несколько недель я экспериментировал с DuckDB. Не буду врать, он мне правда нравится! Но опытные разработчики справедливо относятся скептически к добавлению новой технологии, которая пересекается с тем, что и так отлично работает. Так почему бы просто не использовать Postgres? 

Postgres мне тоже очень нравится, и я правда считаю, что зачастую можно просто использовать его. Но, хотя обе технологии работают с табличными данными, они ориентированы на разные виды задач. DuckDB, на мой взгляд, в первую очередь инструмент для аналитики или ELT, и в этом он действительно силён. Postgres умеет многое из того, что умеет DuckDB, но обычно не так быстро и не так просто. Я бы не стал использовать DuckDB для транзакционных нагрузок, так что он не заменит Postgres ни в одном из сценариев, где я применяю именно Postgres.

Я хочу провести несколько базовых тестов, проверить, что моё понимание верное, и подкрепить утверждения цифрами. Поэтому я снова скачал данные с data.entur.no, а именно национальные норвежские записи real-time данных общественного транспорта за январь и февраль 2025 года. Если хотите повторить эксперимент, можно скачать файл DuckDB отсюда (5 GB).

Это Jupyter блокнот, так что мы будем сочетать код и текст и показывать много вывода программ. Для начала быстро сориентируемся:

import duckdb

db = duckdb.connect('all.db')

%load_ext sql
%config SqlMagic.displaylimit=50
%sql db --alias duckdb

%sql select count() from arrivals;

Выполнение запроса в 'duckdb'

count_star()

85079666

Итак, здесь у нас около 85 миллионов строк со следующей схемой:

%%sql
DESCRIBE ARRIVALS

Выполнение запроса в 'duckdb'

column_name

column_type

null

key

default

extra

recordedAtTime

TIMESTAMP WITH TIME ZONE

YES

None

None

None

lineRef

VARCHAR

YES

None

None

None

directionRef

VARCHAR

YES

None

None

None

operatingDate

DATE

YES

None

None

None

vehicleMode

VARCHAR

YES

None

None

None

extraJourney

BOOLEAN

YES

None

None

None

journeyCancellation

BOOLEAN

YES

None

None

None

stopPointRef

VARCHAR

YES

None

None

None

sequenceNr

BIGINT

YES

None

None

None

stopPointName

VARCHAR

YES

None

None

None

originName

VARCHAR

YES

None

None

None

destinationName

VARCHAR

YES

None

None

None

extraCall

BOOLEAN

YES

None

None

None

stopCancellation

BOOLEAN

YES

None

None

None

estimated

BOOLEAN

YES

None

None

None

aimedArrivalTime

TIMESTAMP WITH TIME ZONE

YES

None

None

None

arrivalTime

TIMESTAMP WITH TIME ZONE

YES

None

None

None

aimedDepartureTime

TIMESTAMP WITH TIME ZONE

YES

None

None

None

departureTime

TIMESTAMP WITH TIME ZONE

YES

None

None

None

datedServiceJourneyId

VARCHAR

YES

None

None

None

dataSource

VARCHAR

YES

None

None

None

dataSourceName

VARCHAR

YES

None

None

None

Размер файла базы данных около 5 GB:

!du -hs all.db
5,1G	all.db

Я хочу загрузить эти данные в базу Postgres, чтобы можно было сравнить результаты. Я установил postgres-17 на своей машине из postgres apt. Добавлю бинарники в PATH, чтобы можно было легко поднять собственный экземпляр Postgres. На этой машине 64 GB оперативной памяти, и пользоваться ей буду только я, так что Postgres можно выделить щедрые ресурсы.

import os
import string
import random
os.environ['PATH'] = '/usr/lib/postgresql/17/bin:' + os.environ['PATH']
# Генерируем случайный пароль и кладём его в переменную окружения, чтобы все библиотеки Postgres могли его найти.
os.environ['PGPASSWORD'] = ''.join(random.choices(string.ascii_letters + string.digits, k=20))

После этого можно использовать initdb, чтобы создать кластер/экземпляр Postgres в каталоге pgtemp внутри текущей рабочей директории:

%%bash

# Создаём кластер базы данных, где:
# суперпользователь называется postgres
# каждое соединение/воркер может использовать 8 GB RAM
# менеджер буферов может использовать 24 GB RAM для кэширования таблиц
# всё размещается в каталоге pgtemp
initdb -U postgres \
 --set work_mem=8GB \
 --set shared_buffers=24GB \
 --set maintenance_work_mem=8GB \
 --set listen_addresses=127.0.0.1 \
 --set port=5433 \
 --set unix_socket_directories="$(pwd)/pgsockets" \
 -D pgtemp
...
Успех. Теперь вы можете запустить сервер базы данных при помощи:

    pg_ctl -D pgtemp -l logfile start
...

Давайте перепроверим, какие настройки в итоге применились, чтобы потом не удивиться, если вдруг где-то остался work_mem по умолчанию:

!grep -E 'work_mem|shared_buffers|listen' pgtemp/postgresql.conf
listen_addresses = '127.0.0.1'		# what IP address(es) to listen on;
shared_buffers = 24GB			# min 128kB
work_mem = 8GB				# min 64kB
#hash_mem_multiplier = 2.0		# 1-1000.0 multiplier on hash table work_mem
maintenance_work_mem = 8GB		# min 64kB
#autovacuum_work_mem = -1		# min 64kB, or -1 to use maintenance_work_mem
#logical_decoding_work_mem = 64MB	# min 64kB
#wal_buffers = -1			# min 32kB, -1 sets based on shared_buffers

Выглядит хорошо, запускаем!

!mkdir -p pgsockets
!pg_ctl -D pgtemp -l pg.log start
waiting for server to start.... done
server started

Теперь у нас есть Postgres на 127.0.0.1:5433! Подключим к нему DuckDB, чтобы можно было легко загрузить туда данные. Мы создадим unlogged-таблицу, которая не пишет журналы транзакций, так как для этого эксперимента они не нужны. Это должно немного ускорить вставку данных. Но это также означает, что в случае падения Postgres такая таблица, по сути, будет потеряна, так что прежде чем делать так с чем-то важным, хорошо подумайте.

%%sql

ATTACH 'dbname=postgres user=postgres host=127.0.0.1 port=5433' AS pgtemp (TYPE postgres);

CREATE TABLE pgtemp.arrivals(
    recordedAtTime TIMESTAMP WITH TIME ZONE,
    lineRef VARCHAR,
    directionRef VARCHAR,
    operatingDate DATE,
    vehicleMode VARCHAR,
    extraJourney BOOLEAN,
    journeyCancellation BOOLEAN,
    stopPointRef VARCHAR,
    sequenceNr BIGINT,
    stopPointName VARCHAR,
    originName VARCHAR,
    destinationName VARCHAR,
    extraCall BOOLEAN,
    stopCancellation BOOLEAN,
    estimated BOOLEAN,
    aimedArrivalTime TIMESTAMP WITH TIME ZONE,
    arrivalTime TIMESTAMP WITH TIME ZONE,
    aimedDepartureTime TIMESTAMP WITH TIME ZONE,
    departureTime TIMESTAMP WITH TIME ZONE,
    datedServiceJourneyId VARCHAR,
    dataSource VARCHAR,
    dataSourceName VARCHAR);

Выполнение запроса в 'duckdb'

Сделаем таблицу unlogged:

%%sql
CALL postgres_execute('pgtemp', 'ALTER TABLE arrivals SET UNLOGGED;')

Выполнение запроса в 'duckdb'

Success

Поскольку схемы таблиц совпадают, мы должны суметь эффективно загрузить данные оптом через DuckDB. Давайте попробуем:

%%sql
INSERT INTO pgtemp.arrivals BY NAME SELECT * FROM arrivals;

Выполнение запроса в 'duckdb'

Count

85079666

Окей, первое, что я хочу узнать, это какой размер у базы после этого. Проверим:

!du -hs pgtemp
18G	  pgtemp

Меня это не удивляет: колоночные форматы хранения куда проще эффективно сжимать, чем строковые, вроде того, который использует Postgres. Для Postgres это должно поместиться в память: у него 24 GB RAM под shared buffers (и щедрые 8 GB под сортировки и тому подобное). Давайте замерим время выполнения нескольких базовых операций в DuckDB и Postgres. DuckDB мы ограничим 16 GB RAM, чтобы машине осталось чем заняться… всем остальным.

# limits for DuckDB
%sql set memory_limit = '16GB';
%sql set threads = 11; -- CPU has 12 physical cores, Ryzen 9 5900X.
%time db.sql("select dataSource, count(*) from arrivals group by dataSource").df()
%time db.sql("""call postgres_query('pgtemp', 'select "dataSource", count(*) from arrivals group by "dataSource"')""").df()
CPU times: user 316 ms, sys: 11.3 ms, total: 327 ms
Wall time: 31.6 ms

CPU times: user 11.3 s, sys: 854 μs, total: 11.3 s
Wall time: 11.3 s

Я отбросил вывод результирующего набора, оставив только времена выполнения. В этом очень простом примере DuckDB справляется за 31.6 ms, а Postgresу требуется 11.3 секунды.

Но это сравнение очень нечестное. В данном случае DuckDB смотрит всего на один столбец, который, скорее всего, закодирован словарём и сжатием повторов, примерно так же, как это делают Arrow и Parquet. Подробнее об этом можно почитать здесь и здесь. Такой запрос и такое распределение данных по сути являются лучшим сценарием для колоночных форматов. Индекс помог бы Postgres, но он, вероятно, был бы заметно больше, чем столбец в хранении DuckDB, и потому всё равно работал бы медленнее.

Будет честнее попробовать сделать group by по столбцу, где такие «хитрости» невозможны. Давайте посчитаем регистрации по часам суток: это заставит обе реализации просмотреть все значения столбца recordedAtTime.

q = 'select count(*), extract(hour from "recordedAtTime") as hour from arrivals group by 2'
%time db.sql(q).df()
%time db.sql(f"call postgres_query('pgtemp', '{q}')").df()
CPU times: user 18 s, sys: 0 ns, total: 18 s
Wall time: 1.65 s

CPU times: user 5.97 s, sys: 0 ns, total: 5.97 s
Wall time: 5.97 s

На этот раз разница куда менее драматичная. DuckDB нужно 1.65 сек, а Postgres справляется примерно за 6 сек. В этом запросе DuckDB пришлось проверить каждое значение в столбце, здесь гораздо меньше работы, которую можно просто пропустить. Если посмотреть на данные по CPU time, становится очевидно, что DuckDB тратит на вычисление заметно больше процессорного времени, чем Postgres, из-за параллелизации.

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

q = '''
with times as (
    select 
      extract(epoch from 
          lead("arrivalTime", 1) over (partition by "datedServiceJourneyId" order by "sequenceNr") 
           - "arrivalTime"
      ) as timedelta
    from arrivals
)
select
    max(timedelta), min(timedelta), sum(timedelta) / count(*) -- avg in postgres and mean in duckdb
from times
'''
%time db.sql(q).df()
%time db.sql(f"call postgres_query('pgtemp', '{q}')").df()
CPU times: user 2min 40s, sys: 9.72 s, total: 2min 50s
Wall time: 15.7 s

CPU times: user 8min 15s, sys: 52.2 ms, total: 8min 15s
Wall time: 8min 15s

Что ж, разница приличная. DuckDB потратил 15.7 секунд, а Postgres потратил 8 минут 15 секунд. Я не уверен, почему разница здесь настолько большая. Даже последовательное чтение таблицы для Postgres сейчас должно быть довольно быстрым, данные ведь уже точно в RAM. Разрыв настолько велик, что я не могу объяснить его без участия дисковых операций.

В такой ситуации было бы гораздо быстрее вытащить все данные из Postgres в DuckDB и делать агрегацию уже там. Похоже, мы можем помочь Postgres, создав индекс, который совпадает с разбиением (partition) в оконной функции. Давайте сделаем это.

index = 'create index on arrivals("datedServiceJourneyId", "sequenceNr");'
%time db.execute(f"call postgres_execute('pgtemp', '{index}');")
CPU times: user 3min 3s, sys: 954 ms, total: 3min 4s
Wall time: 3min 3s

3 минуты, меньше, чем выполнение запроса изначально. Давайте запустим запрос ещё раз:

%time db.sql(f"call postgres_query('pgtemp', '{q}')").df()
CPU times: user 8min 16s, sys: 432 ms, total: 8min 17s
Wall time: 8min 16s

Да, индекс оказался бесполезным. Возможно, потому что таблица и так целиком помещается в память. На всякий случай я сделал ANALYZE и попробовал ещё раз. На диске этот индекс больше, чем весь файл DuckDB. После его создания размер кластера Postgres в pgtemp вырос до 25 GB:

!du -hs pgtemp
25G	pgtemp

Есть ещё один вариант, который мы пока не пробовали: выполнить запрос в DuckDB, но по таблице в Postgres (то есть DuckDB придётся либо делегировать часть работы Postgres, либо выкачать все данные к себе).

%time db.sql(q.replace("arrivals", "pgtemp.arrivals")).df()
CPU times: user 3min, sys: 13.6 s, total: 3min 14s
Wall time: 25.7 s

Это очень интересно: 25.7 секунд по сравнению с 8 минутами при выполнении целиком в Postgres или 13.4 секунд при выполнении целиком в DuckDB. Разумеется, это создаст нагрузку и I/O на сервере Postgres, но, возможно, это хороший способ вынести тяжёлые вычисления на сервер приложений?

Я собираюсь разобраться, что именно происходит в Postgres: мне не кажется логичным, что выполнение запроса занимает 8 минут, если всё уже в памяти. Пойду в psql и посмотрю, что скажет explain (analyze on, buffers on).

postgres=# explain (analyze on, buffers on) with times as (
    select 
      extract(epoch from 
          lead("arrivalTime", 1) over (partition by "datedServiceJourneyId" order by "sequenceNr") 
           - "arrivalTime"
      ) as timedelta
    from arrivals
)
select
    max(timedelta), min(timedelta), sum(timedelta) / count(*) -- avg in postgres and mean in duckdb
from times;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18207075.69..18207075.71 rows=1 width=96) (actual time=506560.516..506560.517 rows=1 loops=1)
   Buffers: shared hit=2729 read=2314831, temp read=813011 written=813012
   ->  WindowAgg  (cost=14377525.67..16505053.45 rows=85101112 width=90) (actual time=467725.139..501383.481 rows=85079666 loops=1)
         Buffers: shared hit=2729 read=2314831, temp read=813011 written=813012
         ->  Sort  (cost=14377525.65..14590278.43 rows=85101112 width=66) (actual time=467725.100..474901.976 rows=85079666 loops=1)
               Sort Key: arrivals."datedServiceJourneyId", arrivals."sequenceNr"
               Sort Method: external merge  Disk: 6504088kB
               Buffers: shared hit=2729 read=2314831, temp read=813011 written=813012
               ->  Seq Scan on arrivals  (cost=0.00..3168571.12 rows=85101112 width=66) (actual time=199.763..17347.756 rows=85079666 loops=1)
                     Buffers: shared hit=2729 read=2314831
 Planning:
   Buffers: shared hit=22
 Planning Time: 0.371 ms
 JIT:
   Functions: 11
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.608 ms (Deform 0.255 ms), Inlining 64.518 ms, Optimization 68.888 ms, Emission 66.374 ms, Total 200.389 ms
 Execution Time: 507455.248 ms

Ага, сортировка уходит на диск. Это означает, что может помочь увеличение work_mem. Я попробую удвоить его до 16 GB через set work_mem='16GB'; (и закрою на этой машине пару приложений). Также я создам индекс, включающий все три столбца, которые использует этот запрос, чтобы попытаться получить Index Only Scan:

postgres=# create index on arrivals("datedServiceJourneyId", "sequenceNr", "arrivalTime");
CREATE INDEX
postgres=# explain (analyze on, buffers on) with times as (
    select 
      extract(epoch from 
          lead("arrivalTime", 1) over (partition by "datedServiceJourneyId" order by "sequenceNr") 
           - "arrivalTime"
      ) as timedelta
    from arrivals
)
select
    max(timedelta), min(timedelta), sum(timedelta) / count(*) -- avg in postgres and mean in duckdb
from times;
                                                                                               QUERY PLAN                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8735893.91..8735893.93 rows=1 width=96) (actual time=46947.869..46947.870 rows=1 loops=1)
   Buffers: shared hit=51347699 read=947841
   ->  WindowAgg  (cost=0.77..7034300.63 rows=85079664 width=90) (actual time=61.489..41859.920 rows=85079666 loops=1)
         Buffers: shared hit=51347699 read=947841
         ->  Index Only Scan using "arrivals_datedServiceJourneyId_sequenceNr_arrivalTime_idx" on arrivals  (cost=0.69..5120008.19 rows=85079664 width=66) (actual time=61.471..12268.748 rows=85079666 loops=1)
               Heap Fetches: 0
               Buffers: shared hit=51347699 read=947841
 Planning:
   Buffers: shared hit=22 read=1 dirtied=2
 Planning Time: 14.759 ms
 JIT:
   Functions: 10
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.499 ms (Deform 0.116 ms), Inlining 11.793 ms, Optimization 28.170 ms, Emission 21.494 ms, Total 61.955 ms
 Execution Time: 46950.351 ms
(15 rows)

Вот это уже похоже на правду. То есть если «подпереть» запрос подходящим индексом, Postgres может выполнить его за 47 секунд, что довольно близко к DuckDB, учитывая, что Postgres не задействует все мои ядра. Но этот индекс весит 7504 MB и на его создание ушло несколько минут. Иными словами, чтобы получить хорошую производительность, нужно заранее понимать, что этот запрос надо подкреплять индексом. Если бы это был сервер баз данных с транзакционной нагрузкой, выполнение такого запроса без индекса могло бы иметь последствия. Кроме того, здесь мы полагаемся на то, что индекс находится в памяти, чтобы получить хорошую скорость.

Выводы

Коротко:

  • DuckDB не умеет выполнять конкурентные транзакции записи. А Postgres для этого отлично подходит.

  • Postgres умеет распараллеливать запросы, но DuckDB делает это гораздо агрессивнее.

  • DuckDB более терпим к аналитическим запросам, если вы не настроили правильные индексы.

  • DuckDB, скорее всего, будет намного быстрее для аналитических запросов на больших наборах данных.

  • DuckDB хранит данные намного компактнее.

  • Postgres отлично справляется со справедливым распреде��ением вычислительных ресурсов между большим числом параллельных пользователей.

  • Но вместе они тоже работают отлично!

Наведу порядок после этого эксперимента и на этом собираюсь закончить. Я правда думаю, что Postgres может делать почти всё то же, что и DuckDB. Но я вполне вижу сценарий, где имеет смысл использовать оба: они хорошо дополняют возможности друг друга.

!pg_ctl stop -D pgtemp
!rm -rf pgtemp
waiting for server to shut down...... done
server stopped

Если вам близка работа с реальными данными и вопросами «почему система ведёт себя так», логичным шагом становится углубление в продуктовую аналитику. Курс поможет системно прокачать SQL и Python, проверять гипотезы через A/B-тесты и превращать сырые цифры в осмысленные продуктовые выводы.

Чтобы узнать больше о формате обучения и познакомиться с преподавателями, приходите на бесплатные демо-уроки:

  • 27 января 20:00. «AI вместе с PostgreSQL». Записаться

  • 3 февраля 20:00. «Чек-лист идеального A/B-теста». Записаться

  • 9 февраля 20:00. «Проектирование Data Vault по набору данных TPC-H с применением dbt и Trino». Записаться