By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36289 in the last week).
CREATE TABLE first (
id INT PRIMARY KEY,
title TEXT
);
✓
hidden batch(es)
CREATE TABLE second (
id INT PRIMARY KEY,
first_id INT,
title TEXT
);
CREATE INDEX second_first_id ON second (first_id);
✓
✓
hidden batch(es)
CREATE TABLE third (
id INT PRIMARY KEY,
second_id INT,
title TEXT
);
CREATE INDEX third_second_id ON third (second_id);
✓
✓
hidden batch(es)
CREATE TABLE fourth (
id INT PRIMARY KEY,
third_id INT,
title TEXT
);
CREATE INDEX fourth_third_id ON fourth (third_id);
✓
✓
hidden batch(es)
CREATE TABLE fifth (
id INT PRIMARY KEY,
fourth_id INT,
title TEXT
);
CREATE INDEX fifth_fourth_id ON fifth (fourth_id);
✓
✓
hidden batch(es)
WITH numbers AS (
SELECT *
FROM generate_series(1, 10000)
)
INSERT INTO first(id, title)
SELECT
numbers.generate_series,
md5(random()::text)
FROM numbers;
10000 rows affected
hidden batch(es)
WITH numbers AS (
SELECT *
FROM generate_series(1, 100000)
)
INSERT INTO second(id, first_id, title)
SELECT
numbers.generate_series,
(round(random() * (10000 - 1)) + 1) AS first_id,
md5(random()::text)
FROM numbers;
100000 rows affected
hidden batch(es)
WITH numbers AS (
SELECT *
FROM generate_series(1, 100000)
)
INSERT INTO third(id, second_id, title)
SELECT
numbers.generate_series,
(round(random() * (60000 - 1)) + 1) AS second_id,
md5(random()::text)
FROM numbers;
100000 rows affected
hidden batch(es)
WITH numbers AS (
SELECT *
FROM generate_series(1, 100000)
)
INSERT INTO fourth(id, third_id, title)
SELECT
numbers.generate_series,
(round(random() * (60000 - 1)) + 1) AS third_id,
md5(random()::text)
FROM numbers;
100000 rows affected
hidden batch(es)
WITH numbers AS (
SELECT *
FROM generate_series(1, 100000)
)
INSERT INTO fifth(id, fourth_id, title)
SELECT
numbers.generate_series,
(round(random() * (60000 - 1)) + 1) AS fourth_id,
md5(random()::text)
FROM numbers;
100000 rows affected
hidden batch(es)
CREATE TABLE all_json (
id INTEGER NOT NULL,
data JSONB DEFAULT '{}'
);
CREATE INDEX all_json_data ON all_json USING gin(data);
✓
✓
hidden batch(es)
WITH fifth_json AS (
SELECT
fourth_id,
coalesce(json_agg(json_build_object(
'id', id,
'title', title
)), '[]'::json) AS jsoned
FROM fifth
GROUP BY fourth_id
),
fourth_json AS (
SELECT
third_id,
coalesce(json_agg(json_build_object(
'id', id,
'title', title,
'fifth', fifth_json.jsoned
)), '[]'::json) AS jsoned
FROM fourth
LEFT JOIN fifth_json
ON fifth_json.fourth_id = id
GROUP BY third_id
),
third_json AS (
SELECT
second_id,
coalesce(json_agg(json_build_object(
'id', id,
'title', title,
'fourth', fourth_json.jsoned
)), '[]'::json) AS jsoned
FROM third
LEFT JOIN fourth_json
ON fourth_json.third_id = id
GROUP BY second_id
),
second_json AS (
SELECT
first_id,
coalesce(json_agg(json_build_object(
'id', id,
'title', title,
'third', third_json.jsoned
)), '[]'::json) AS jsoned
FROM second
LEFT JOIN third_json
ON third_json.second_id = id
GROUP BY first_id
)
INSERT INTO all_json(id, data)
SELECT
id,
json_build_object(
'id', id,
'title', title,
'second', coalesce(json_agg(second_json.jsoned), '[]'::json)
) AS jsoned
FROM first
LEFT JOIN second_json
ON second_json.first_id = id
GROUP BY id
10000 rows affected
hidden batch(es)
SELECT
COUNT(DISTINCT first.id)
FROM first
LEFT JOIN second
ON second.first_id = first.id
LEFT JOIN third
ON third.second_id = second.id
LEFT JOIN fourth
ON fourth.third_id = third.id
LEFT JOIN fifth
ON fifth.fourth_id = fourth.id
WHERE
fifth.id > 30000
count
8997
…
hidden batch(es)
SELECT COUNT(id)
FROM all_json
WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)'
count
8997
…
hidden batch(es)
EXPLAIN ANALYZE
SELECT
*
FROM first
LEFT JOIN second
ON second.first_id = first.id
LEFT JOIN third
ON third.second_id = second.id
LEFT JOIN fourth
ON fourth.third_id = third.id
LEFT JOIN fifth
ON fifth.fourth_id = fourth.id
WHERE
fifth.id > 30000
