KDOC 658: 集計や並べ替えの後ろにある LIMIT は読む行を減らさないのを確かめる

SET max_parallel_workers_per_gather = 0;
CREATE TABLE users (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int PRIMARY KEY, user_id int);
CREATE TABLE items (order_id int, price int);
INSERT INTO users
SELECT i, 'user' || i FROM generate_series(1, 10000) i;
INSERT INTO orders
SELECT i, i % 10000 + 1 FROM generate_series(1, 100000) i;
INSERT INTO items
SELECT i % 100000 + 1, i % 1000 FROM generate_series(1, 300000) i;
ANALYZE users;
ANALYZE orders;
ANALYZE items;

-- 集計して並べ替えてから 5 件取る
EXPLAIN (ANALYZE, FORMAT JSON, COSTS OFF, TIMING OFF,
         SUMMARY OFF, BUFFERS OFF)
SELECT u.name, sum(i.price) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN items i ON i.order_id = o.id
WHERE u.id < 100
GROUP BY u.name
ORDER BY total DESC
LIMIT 5;

-- 集計も並べ替えもせずに 5 件取る
EXPLAIN (ANALYZE, FORMAT JSON, COSTS OFF, TIMING OFF,
         SUMMARY OFF, BUFFERS OFF)
SELECT u.name, i.price
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN items i ON i.order_id = o.id
WHERE u.id < 100
LIMIT 5;