🚀
PostgreSQLで最新の値を取得する その２
はじめに
前回の記事では単純に最新の値を取ってくるだけで終わってしまったので本記事では簡易的にパフォーマンスについて比較したいと思う。
postgresqlのバージョンは前回と同様に16で検証する。
テーブル定義
テーブル構造は前回と同様でデータ件数を100万件にしてます。
CREATE TABLE IF NOT EXISTS
exam (
id UUID DEFAULT gen_random_uuid (),
student_id UUID DEFAULT gen_random_uuid (),
scoring_date TIMESTAMP NOT NULL,
VALUE INT NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (id)
);
junpee=# \d exam
Table "public.exam"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
student_id | uuid | | | gen_random_uuid()
scoring_date | timestamp without time zone | | not null |
value | integer | | not null |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
WITH
student AS (
SELECT
gen_random_uuid () AS id
FROM
GENERATE_SERIES(1, 1000)
)
INSERT INTO
exam (student_id, scoring_date, VALUE)
SELECT
student.id,
'2020-01-01'::date + (INTERVAL '1 day') * ROUND(RANDOM() * 365 * 2) AS scoring_date,
(RANDOM() * 100)::INT
FROM
student,
GENERATE_SERIES(1, 1000);
junpee=# select count(*) from exam ;
count
---------
1000000
(1 row)
IN句を使う
実行計画を出力して比較してみます。
インデックスなどを作成せずに比較するとIN句を使った場合が一番速くなりました。
パラレルクエリとして実行されているようなのでmax_parallel_workers_per_gatherを上げるとパフォーマンスアップを期待できそうです。
ちなみに、JOINに書き換えた場合でも同じような実行計画になりました。
EXPLAIN
ANALYZE
SELECT
student_id,
scoring_date,
VALUE
FROM
exam
WHERE
(student_id, scoring_date) IN (
SELECT
student_id,
MAX(scoring_date)
FROM
exam
GROUP BY
student_id
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=16924.20..41520.22 rows=1368 width=28) (actual time=76.551..205.239 rows=1635 loops=1)
Hash Cond: ((exam.student_id = exam_1.student_id) AND (exam.scoring_date = (max(exam_1.scoring_date))))
-> Seq Scan on exam (cost=0.00..19346.00 rows=1000000 width=28) (actual time=0.009..47.446 rows=1000000 loops=1)
-> Hash (cost=16909.20..16909.20 rows=1000 width=24) (actual time=76.533..76.599 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 63kB
-> Finalize GroupAggregate (cost=16655.85..16909.20 rows=1000 width=24) (actual time=75.592..76.471 rows=1000 loops=1)
Group Key: exam_1.student_id
-> Gather Merge (cost=16655.85..16889.20 rows=2000 width=24) (actual time=75.587..76.006 rows=2789 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=15655.83..15658.33 rows=1000 width=24) (actual time=65.991..66.037 rows=930 loops=3)
Sort Key: exam_1.student_id
Sort Method: quicksort Memory: 70kB
Worker 0: Sort Method: quicksort Memory: 67kB
Worker 1: Sort Method: quicksort Memory: 67kB
-> Partial HashAggregate (cost=15596.00..15606.00 rows=1000 width=24) (actual time=65.716..65.847 rows=930 loops=3)
Group Key: exam_1.student_id
Batches: 1 Memory Usage: 129kB
Worker 0: Batches: 1 Memory Usage: 129kB
Worker 1: Batches: 1 Memory Usage: 129kB
-> Parallel Seq Scan on exam exam_1 (cost=0.00..13512.67 rows=416667 width=24) (actual time=0.006..18.053 rows=333333 loops=3)
Planning Time: 0.123 ms
Execution Time: 205.342 ms
(23 rows)
Time: 206.032 ms
max_parallel_workers_per_gatherをmax_worker_processesと同じにして再度実行してみます。
SET max_parallel_workers_per_gather = 8;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=25371.00..39773.16 rows=1368 width=28) (actual time=187.120..250.309 rows=1635 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Hash Join (cost=24371.00..38636.36 rows=441 width=28) (actual time=200.115..233.719 rows=409 loops=4)
Hash Cond: ((exam.student_id = exam_1.student_id) AND (exam.scoring_date = (max(exam_1.scoring_date))))
-> Parallel Seq Scan on exam (cost=0.00..12571.81 rows=322581 width=28) (actual time=0.005..12.129 rows=250000 loops=4)
-> Hash (cost=24356.00..24356.00 rows=1000 width=24) (actual time=200.012..200.013 rows=1000 loops=4)
Buckets: 1024 Batches: 1 Memory Usage: 63kB
-> HashAggregate (cost=24346.00..24356.00 rows=1000 width=24) (actual time=199.719..199.874 rows=1000 loops=4)
Group Key: exam_1.student_id
Batches: 1 Memory Usage: 129kB
Worker 0: Batches: 1 Memory Usage: 129kB
Worker 1: Batches: 1 Memory Usage: 129kB
Worker 2: Batches: 1 Memory Usage: 129kB
-> Seq Scan on exam exam_1 (cost=0.00..19346.00 rows=1000000 width=24) (actual time=0.009..53.010 rows=1000000 loops=4)
Planning Time: 0.106 ms
Execution Time: 250.406 ms
(17 rows)
Time: 251.043 ms
むしろ、遅くなってしまいました。複数のworkerで実行する分オーバーヘッドの方が大きくなってしまったかもしれません。
ウィンドウ関数を使う
EXPLAIN
ANALYZE
SELECT
ex.student_id,
ex.scoring_date,
ex.VALUE
FROM
(
SELECT
student_id,
scoring_date,
VALUE,
ROW_NUMBER() OVER (
PARTITION BY
student_id
ORDER BY
scoring_date DESC
) AS RANK
FROM
exam
) ex
WHERE
RANK = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on ex (cost=142929.84..175429.84 rows=5000 width=28) (actual time=504.593..623.071 rows=1000 loops=1)
Filter: (ex.rank = 1)
-> WindowAgg (cost=142929.84..162929.84 rows=1000000 width=36) (actual time=504.592..622.933 rows=1000 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Sort (cost=142929.84..145429.84 rows=1000000 width=28) (actual time=504.583..570.664 rows=1000000 loops=1)
Sort Key: exam.student_id, exam.scoring_date DESC
Sort Method: external merge Disk: 37232kB
-> Seq Scan on exam (cost=0.00..19346.00 rows=1000000 width=28) (actual time=0.007..87.080 rows=1000000 loops=1)
Planning Time: 0.132 ms
Execution Time: 626.785 ms
(10 rows)
Time: 627.467 ms
ウィンドウ関数とDISTINCT ON句を使った場合では、ほとんど同じ速度になりました。
DISTINCT ON句を使う
EXPLAIN
ANALYZE
SELECT DISTINCT
ON (student_id) student_id,
scoring_date,
VALUE
FROM
exam
ORDER BY
student_id,
scoring_date DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Unique (cost=142929.84..147929.84 rows=1000 width=28) (actual time=491.008..606.000 rows=1000 loops=1)
-> Sort (cost=142929.84..145429.84 rows=1000000 width=28) (actual time=491.007..556.759 rows=1000000 loops=1)
Sort Key: student_id, scoring_date DESC
Sort Method: external merge Disk: 37232kB
-> Seq Scan on exam (cost=0.00..19346.00 rows=1000000 width=28) (actual time=0.009..85.635 rows=1000000 loops=1)
Planning Time: 0.044 ms
Execution Time: 609.658 ms
(7 rows)
Time: 610.153 ms
インデックスを作成する
ウィンドウ関数とDISTINCT ON句を使用したSQLの場合、student_id と scoring_dateでsortするときに時間がかかってるようなのでインデックスを貼って再度実行してみます。
CREATE INDEX idx_student_id_and_scoring_date ON exam (student_id, scoring_date DESC);
IN句
HashAggregate が Partial HashAggregateに変わったことで非常に速くなりました。
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=15245.01..23379.58 rows=1368 width=28) (actual time=66.954..73.119 rows=1635 loops=1)
-> Finalize GroupAggregate (cost=15244.58..15624.58 rows=1000 width=24) (actual time=66.929..68.074 rows=1000 loops=1)
Group Key: exam_1.student_id
-> Gather Merge (cost=15244.58..15599.58 rows=3000 width=24) (actual time=66.923..67.573 rows=3077 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Sort (cost=14244.54..14247.04 rows=1000 width=24) (actual time=54.967..55.011 rows=769 loops=4)
Sort Key: exam_1.student_id
Sort Method: quicksort Memory: 59kB
Worker 0: Sort Method: quicksort Memory: 56kB
Worker 1: Sort Method: quicksort Memory: 63kB
Worker 2: Sort Method: quicksort Memory: 64kB
-> Partial HashAggregate (cost=14184.71..14194.71 rows=1000 width=24) (actual time=54.697..54.827 rows=769 loops=4)
Group Key: exam_1.student_id
Batches: 1 Memory Usage: 129kB
Worker 0: Batches: 1 Memory Usage: 129kB
Worker 1: Batches: 1 Memory Usage: 129kB
Worker 2: Batches: 1 Memory Usage: 129kB
-> Parallel Seq Scan on exam exam_1 (cost=0.00..12571.81 rows=322581 width=24) (actual time=0.007..15.269 rows=250000 loops=4)
-> Index Scan using idx_student_id_and_scoring_date on exam (cost=0.42..7.74 rows=1 width=28) (actual time=0.004..0.005 rows=2 loops=1000)
Index Cond: ((student_id = exam_1.student_id) AND (scoring_date = (max(exam_1.scoring_date))))
Planning Time: 0.165 ms
Execution Time: 73.255 ms
(23 rows)
Time: 73.998 ms
ウィンドウ関数
45%ぐらい速くなりました。
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on ex (cost=0.42..96312.30 rows=5000 width=28) (actual time=0.021..341.251 rows=1000 loops=1)
Filter: (ex.rank = 1)
-> WindowAgg (cost=0.42..83812.30 rows=1000000 width=36) (actual time=0.020..341.055 rows=1000 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Index Scan using idx_student_id_and_scoring_date on exam (cost=0.42..66312.30 rows=1000000 width=28) (actual time=0.016..294.314 rows=1000000 loops=1)
Planning Time: 0.178 ms
Execution Time: 341.342 ms
(7 rows)
Time: 342.094 ms
DISTINCT ON句
こちらもインデックスを貼った後に実行すれば50%程度速くなりました。
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.42..68812.30 rows=1000 width=28) (actual time=0.024..305.611 rows=1000 loops=1)
-> Unique (cost=0.42..68812.30 rows=1000 width=28) (actual time=0.022..305.411 rows=1000 loops=1)
-> Index Scan using idx_student_id_and_scoring_date on exam (cost=0.42..66312.30 rows=1000000 width=28) (actual time=0.021..249.511 rows=1000000 loops=1)
Planning Time: 0.056 ms
Execution Time: 305.692 ms
(5 rows)
Time: 306.225 ms
おわりに
IN句を使用した場合が一番速くなりました。MAX(scoring_date)が重複すると複数行出力されるので、DISTINCT ON句と合わせて使用するとよいかもしれません。
Discussion