By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
SHOW server_version;
CREATE TABLE category (id integer PRIMARY KEY, name varchar(255) NOT NULL, parent integer REFERENCES category(id));
CREATE TABLE product (id integer PRIMARY KEY, name varchar(255) NOT NULL);
CREATE TABLE category_to_product (category integer NOT NULL REFERENCES category(id), product integer NOT NULL REFERENCES product(id), PRIMARY KEY (category, product));
CREATE TABLE review (id integer PRIMARY KEY, product integer NOT NULL REFERENCES product(id), content varchar(255) NOT NULL);
| server_version |
|---|
| 10.7 (Debian 10.7-1.pgdg80+1) |
INSERT INTO category (id, name) SELECT i, concat('Category ', i) FROM generate_series(1, 50) AS i;
INSERT INTO category (id, name, parent) SELECT i, concat('Sub-category ', i), i % 49 + 1 FROM generate_series(51, 130) AS i;
INSERT INTO product (id, name) SELECT i, concat('Product ', i) FROM generate_series(1, 12000) AS i;
INSERT INTO category_to_product (product, category) SELECT i % 12000 + 1, (random() * 129)::integer + 1 FROM generate_series(0, 19000) AS i ON CONFLICT DO NOTHING;
-- we want only the first 150000 reviews to have some for the searched products...
WITH
cat_prods (ids) AS (
SELECT array_agg(product)::int[]
FROM category_to_product
WHERE product NOT IN (
SELECT product
FROM category_to_product
WHERE category IN (
SELECT 27
UNION ALL
SELECT id FROM category WHERE parent = 27
)
)
),
data (id, product, content) AS (
SELECT
i,
CASE
WHEN i < 150001 THEN ((random() * 11999)::int + 1)
ELSE p.ids[(random() * (array_length(p.ids, 1) - 1))::int + 1]
END,
concat('This is my review #', i)
FROM generate_series(1, 654436) AS i
CROSS JOIN cat_prods AS p
)
INSERT INTO review (id, product, content)
SELECT * FROM data;
50 rows affected
80 rows affected
12000 rows affected
18939 rows affected
654436 rows affected
-- create missing indexes...
CREATE INDEX idx_category_parent ON category USING btree (parent);
CREATE INDEX "category_to_product_B" ON category_to_product USING btree (product);
CREATE INDEX idx_review_product ON review USING btree (product);
PREPARE so_56126280_prisma(int, int, int) AS
SELECT
"Alias"."id"
FROM "review" AS "Alias"
WHERE
"Alias"."id" IN (
SELECT "review"."id"
FROM "review"
WHERE
"review"."product" IN (
SELECT "category_to_product"."product"
FROM "category_to_product"
JOIN "category" AS "category_product_Alias" ON "category_product_Alias"."id" = "category_to_product"."category"
WHERE
"category_product_Alias"."id" = $1
OR
"category_product_Alias"."id" IN (
SELECT "category"."id"
FROM "category"
JOIN "category" AS "category_category_product_Alias" ON "category_category_product_Alias"."id" = "category"."parent"
WHERE "category_category_product_Alias"."id" = $1
)
)
)
ORDER BY "Alias"."id" DESC
LIMIT $2 OFFSET $3;
-- warmup to avoid I/O...
EXECUTE so_56126280_prisma(27, 11, 0);
-- prepare other query variants...
PREPARE so_56126280_laurenz(int, int, int) AS
SELECT
"Alias"."id"
FROM "review" AS "Alias"
WHERE
| id |
|---|
| 149962 |
| 149952 |
| 149892 |
| 149872 |
| 149815 |
| 149798 |
| 149788 |
| 149764 |
| 149758 |
| 149748 |
| 149745 |
ANALYZE category;
ANALYZE product;
ANALYZE category_to_product;
ANALYZE review;
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE so_56126280_prisma(27, 11, 0);
| QUERY PLAN |
|---|
| Limit (cost=6.47..50.19 rows=11 width=4) (actual time=13742.202..13748.872 rows=11 loops=1) |
| Buffers: shared hit=2391278 read=11269 written=84 |
| -> Merge Semi Join (cost=6.47..2158975.45 rows=543198 width=4) (actual time=13742.201..13748.867 rows=11 loops=1) |
| Merge Cond: ("Alias".id = review.id) |
| Buffers: shared hit=2391278 read=11269 written=84 |
| -> Index Only Scan Backward using review_pkey on review "Alias" (cost=0.42..22447.97 rows=654436 width=4) (actual time=0.011..106.613 rows=504692 loops=1) |
| Heap Fetches: 504692 |
| Buffers: shared hit=6 read=5581 written=84 |
| -> Nested Loop Semi Join (cost=6.05..2128101.42 rows=543198 width=4) (actual time=13604.592..13611.205 rows=11 loops=1) |
| Buffers: shared hit=2391272 read=5688 |
| -> Index Scan Backward using review_pkey on review (cost=0.42..22447.97 rows=654436 width=8) (actual time=0.005..140.526 rows=504692 loops=1) |
| Buffers: shared hit=1413 read=5552 |
| -> Hash Join (cost=5.62..8.83 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=504692) |
| Hash Cond: ("category_product_Alias".id = category_to_product.category) |
| Buffers: shared hit=2389859 read=136 |
| -> Seq Scan on category "category_product_Alias" (cost=5.27..8.22 rows=66 width=4) (actual time=0.006..0.018 rows=3 loops=504692) |
| Filter: ((id = 27) OR (hashed SubPlan 1)) |
| Rows Removed by Filter: 127 |
| Buffers: shared hit=504694 |
| SubPlan 1 |
| -> Nested Loop (cost=0.00..5.27 rows=2 width=4) (actual time=0.015..0.024 rows=2 loops=1) |
| Buffers: shared hit=2 |
| -> Seq Scan on category "category_category_product_Alias" (cost=0.00..2.62 rows=1 width=4) (actual time=0.006..0.011 rows=1 loops=1) |
| Filter: (id = 27) |
| Rows Removed by Filter: 129 |
| Buffers: shared hit=1 |
| -> Seq Scan on category (cost=0.00..2.62 rows=2 width=8) (actual time=0.008..0.012 rows=2 loops=1) |
| Filter: (parent = 27) |
| Rows Removed by Filter: 128 |
| Buffers: shared hit=1 |
| -> Hash (cost=0.32..0.32 rows=2 width=8) (actual time=0.004..0.004 rows=2 loops=504692) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=1885165 read=136 |
| -> Index Scan using "category_to_product_B" on category_to_product (cost=0.29..0.32 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=504692) |
| Index Cond: (product = review.product) |
| Buffers: shared hit=1885165 read=136 |
| Planning time: 1.044 ms |
| Execution time: 13748.917 ms |
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE so_56126280_laurenz(27, 11, 0);
| QUERY PLAN |
|---|
| Limit (cost=69570.37..69570.40 rows=11 width=8) (actual time=347.389..347.395 rows=11 loops=1) |
| Buffers: shared hit=1383 read=9592, temp read=1709 written=1695 |
| -> Sort (cost=69570.37..70928.37 rows=543198 width=8) (actual time=347.388..347.388 rows=11 loops=1) |
| Sort Key: (("Alias".id + 0)) DESC |
| Sort Method: top-N heapsort Memory: 26kB |
| Buffers: shared hit=1383 read=9592, temp read=1709 written=1695 |
| -> Hash Semi Join (cost=29115.27..57458.60 rows=543198 width=8) (actual time=130.527..346.606 rows=5126 loops=1) |
| Hash Cond: ("Alias".id = review.id) |
| Buffers: shared hit=1383 read=9592, temp read=1709 written=1695 |
| -> Seq Scan on review "Alias" (cost=0.00..11988.36 rows=654436 width=4) (actual time=0.007..83.956 rows=654436 loops=1) |
| Buffers: shared hit=664 read=4780 |
| -> Hash (cost=20203.30..20203.30 rows=543198 width=4) (actual time=130.152..130.152 rows=5126 loops=1) |
| Buckets: 131072 Batches: 8 Memory Usage: 1048kB |
| Buffers: shared hit=719 read=4812, temp written=7 |
| -> Hash Semi Join (cost=453.97..20203.30 rows=543198 width=4) (actual time=3.010..128.942 rows=5126 loops=1) |
| Hash Cond: (review.product = category_to_product.product) |
| Buffers: shared hit=719 read=4812 |
| -> Seq Scan on review (cost=0.00..11988.36 rows=654436 width=8) (actual time=0.006..64.633 rows=654436 loops=1) |
| Buffers: shared hit=632 read=4812 |
| -> Hash (cost=333.78..333.78 rows=9615 width=4) (actual time=2.986..2.986 rows=419 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 143kB |
| Buffers: shared hit=87 |
| -> Hash Join (cost=9.05..333.78 rows=9615 width=4) (actual time=0.069..2.924 rows=419 loops=1) |
| Hash Cond: (category_to_product.category = "category_product_Alias".id) |
| Buffers: shared hit=87 |
| -> Seq Scan on category_to_product (cost=0.00..273.39 rows=18939 width=8) (actual time=0.006..1.294 rows=18939 loops=1) |
| Buffers: shared hit=84 |
| -> Hash (cost=8.22..8.22 rows=66 width=4) (actual time=0.048..0.048 rows=3 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=3 |
| -> Seq Scan on category "category_product_Alias" (cost=5.27..8.22 rows=66 width=4) (actual time=0.034..0.047 rows=3 loops=1) |
| Filter: ((id = 27) OR (hashed SubPlan 1)) |
| Rows Removed by Filter: 127 |
| Buffers: shared hit=3 |
| SubPlan 1 |
| -> Nested Loop (cost=0.00..5.27 rows=2 width=4) (actual time=0.014..0.022 rows=2 loops=1) |
| Buffers: shared hit=2 |
| -> Seq Scan on category "category_category_product_Alias" (cost=0.00..2.62 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1) |
| Filter: (id = 27) |
| Rows Removed by Filter: 129 |
| Buffers: shared hit=1 |
| -> Seq Scan on category (cost=0.00..2.62 rows=2 width=8) (actual time=0.008..0.011 rows=2 loops=1) |
| Filter: (parent = 27) |
| Rows Removed by Filter: 128 |
| Buffers: shared hit=1 |
| Planning time: 0.862 ms |
| Execution time: 347.449 ms |
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE so_56126280_a_horse(27, 11, 0);
| QUERY PLAN |
|---|
| Limit (cost=0.86..209.48 rows=11 width=4) (actual time=2222.560..2223.436 rows=11 loops=1) |
| Buffers: shared hit=3631477 read=5519 |
| -> Nested Loop Semi Join (cost=0.86..468235.05 rows=24688 width=4) (actual time=2222.560..2223.433 rows=11 loops=1) |
| Buffers: shared hit=3631477 read=5519 |
| -> Index Scan Backward using review_pkey on review (cost=0.42..22447.97 rows=654436 width=8) (actual time=0.008..104.006 rows=504692 loops=1) |
| Buffers: shared hit=1447 read=5518 |
| -> Nested Loop (cost=0.43..0.67 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=504692) |
| Buffers: shared hit=3630030 read=1 |
| -> Index Scan using "category_to_product_B" on category_to_product (cost=0.29..0.32 rows=2 width=8) (actual time=0.001..0.002 rows=2 loops=504692) |
| Index Cond: (product = review.product) |
| Buffers: shared hit=1885294 read=1 |
| -> Index Scan using category_pkey on category (cost=0.14..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=872368) |
| Index Cond: (id = category_to_product.category) |
| Filter: ((id = 27) OR (parent = 27)) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=1744736 |
| Planning time: 0.549 ms |
| Execution time: 2223.461 ms |
-- disable nested loop join and see what PostreSQL comes up with here...
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE so_56126280_prisma(27, 11, 0);
SET enable_nestloop = on;
| QUERY PLAN |
|---|
| Limit (cost=10000080111.64..10000080112.30 rows=11 width=4) (actual time=265.974..266.029 rows=11 loops=1) |
| Buffers: shared hit=11375 read=10551 |
| -> Merge Semi Join (cost=10000080111.64..10000112343.24 rows=543198 width=4) (actual time=265.973..266.023 rows=11 loops=1) |
| Merge Cond: ("Alias".id = review.id) |
| Buffers: shared hit=11375 read=10551 |
| -> Index Only Scan Backward using review_pkey on review "Alias" (cost=0.42..22447.97 rows=654436 width=4) (actual time=0.011..103.627 rows=504692 loops=1) |
| Heap Fetches: 504692 |
| Buffers: shared hit=37 read=5550 |
| -> Sort (cost=10000080111.22..10000081469.21 rows=543198 width=4) (actual time=132.031..132.033 rows=11 loops=1) |
| Sort Key: review.id DESC |
| Sort Method: quicksort Memory: 433kB |
| Buffers: shared hit=11338 read=5001 |
| -> Hash Semi Join (cost=10000001192.24..10000020941.57 rows=543198 width=4) (actual time=6.222..130.699 rows=5126 loops=1) |
| Hash Cond: (review.product = category_to_product.product) |
| Buffers: shared hit=11338 read=5001 |
| -> Seq Scan on review (cost=0.00..11988.36 rows=654436 width=8) (actual time=0.008..64.092 rows=654436 loops=1) |
| Buffers: shared hit=509 read=4935 |
| -> Hash (cost=10000001072.05..10000001072.05 rows=9615 width=4) (actual time=6.197..6.197 rows=419 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 143kB |
| Buffers: shared hit=10829 read=66 |
| -> Merge Join (cost=10000000005.72..10000001072.05 rows=9615 width=4) (actual time=1.264..6.142 rows=419 loops=1) |
| Merge Cond: (category_to_product.category = "category_product_Alias".id) |
| Buffers: shared hit=10829 read=66 |
| -> Index Only Scan using category_to_product_pkey on category_to_product (cost=0.29..908.36 rows=18939 width=8) (actual time=0.009..4.910 rows=18099 loops=1) |
| Heap Fetches: 18099 |
| Buffers: shared hit=10825 read=66 |
| -> Index Only Scan using category_pkey on category "category_product_Alias" (cost=10000000005.43..10000000020.03 rows=66 width=4) (actual time=0.039..0.062 rows=3 loops=1) |
| Filter: ((id = 27) OR (hashed SubPlan 1)) |
| Rows Removed by Filter: 127 |
| Heap Fetches: 130 |
| Buffers: shared hit=4 |
| SubPlan 1 |
| -> Nested Loop (cost=10000000000.00..10000000005.28 rows=2 width=4) (actual time=0.017..0.026 rows=2 loops=1) |
| Buffers: shared hit=2 |
| -> Seq Scan on category (cost=0.00..2.62 rows=2 width=8) (actual time=0.009..0.012 rows=2 loops=1) |
| Filter: (parent = 27) |
| Rows Removed by Filter: 128 |
| Buffers: shared hit=1 |
| -> Materialize (cost=0.00..2.63 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=2) |
| Buffers: shared hit=1 |
| -> Seq Scan on category "category_category_product_Alias" (cost=0.00..2.62 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1) |
| Filter: (id = 27) |
| Rows Removed by Filter: 129 |
| Buffers: shared hit=1 |
| Planning time: 0.859 ms |
| Execution time: 266.075 ms |
EXPLAIN (ANALYZE, BUFFERS)
WITH
cte_reviews (id) AS (
SELECT
"Alias"."id"
FROM "review" AS "Alias"
WHERE
"Alias"."id" IN (
SELECT "review"."id"
FROM "review"
WHERE
"review"."product" IN (
SELECT "category_to_product"."product"
FROM "category_to_product"
JOIN "category" AS "category_product_Alias" ON "category_product_Alias"."id" = "category_to_product"."category"
WHERE
"category_product_Alias"."id" = 27
OR
"category_product_Alias"."id" IN (
SELECT "category"."id"
FROM "category"
JOIN "category" AS "category_category_product_Alias" ON "category_category_product_Alias"."id" = "category"."parent"
WHERE "category_category_product_Alias"."id" = 27
)
)
)
ORDER BY "Alias"."id" ASC
)
SELECT id
FROM cte_reviews
ORDER BY id DESC
LIMIT 11 OFFSET 0;
| QUERY PLAN |
|---|
| Limit (cost=134580.70..134580.73 rows=11 width=4) (actual time=170.760..170.763 rows=11 loops=1) |
| Buffers: shared hit=681 read=6502 |
| CTE cte_reviews |
| -> Merge Semi Join (cost=79373.37..111604.97 rows=543198 width=4) (actual time=127.498..168.605 rows=5126 loops=1) |
| Merge Cond: ("Alias".id = review.id) |
| Buffers: shared hit=681 read=6502 |
| -> Index Only Scan using review_pkey on review "Alias" (cost=0.42..22447.97 rows=654436 width=4) (actual time=0.010..30.145 rows=149963 loops=1) |
| Heap Fetches: 149963 |
| Buffers: shared hit=5 read=1647 |
| -> Sort (cost=79372.95..80730.94 rows=543198 width=4) (actual time=127.479..127.871 rows=5126 loops=1) |
| Sort Key: review.id |
| Sort Method: quicksort Memory: 433kB |
| Buffers: shared hit=676 read=4855 |
| -> Hash Semi Join (cost=453.97..20203.30 rows=543198 width=4) (actual time=3.349..126.663 rows=5126 loops=1) |
| Hash Cond: (review.product = category_to_product.product) |
| Buffers: shared hit=676 read=4855 |
| -> Seq Scan on review (cost=0.00..11988.36 rows=654436 width=8) (actual time=0.008..63.717 rows=654436 loops=1) |
| Buffers: shared hit=668 read=4776 |
| -> Hash (cost=333.78..333.78 rows=9615 width=4) (actual time=3.324..3.324 rows=419 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 143kB |
| Buffers: shared hit=8 read=79 |
| -> Hash Join (cost=9.05..333.78 rows=9615 width=4) (actual time=0.070..3.249 rows=419 loops=1) |
| Hash Cond: (category_to_product.category = "category_product_Alias".id) |
| Buffers: shared hit=8 read=79 |
| -> Seq Scan on category_to_product (cost=0.00..273.39 rows=18939 width=8) (actual time=0.006..1.576 rows=18939 loops=1) |
| Buffers: shared hit=5 read=79 |
| -> Hash (cost=8.22..8.22 rows=66 width=4) (actual time=0.049..0.049 rows=3 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=3 |
| -> Seq Scan on category "category_product_Alias" (cost=5.27..8.22 rows=66 width=4) (actual time=0.035..0.048 rows=3 loops=1) |
| Filter: ((id = 27) OR (hashed SubPlan 1)) |
| Rows Removed by Filter: 127 |
| Buffers: shared hit=3 |
| SubPlan 1 |
| -> Nested Loop (cost=0.00..5.27 rows=2 width=4) (actual time=0.014..0.022 rows=2 loops=1) |
| Buffers: shared hit=2 |
| -> Seq Scan on category "category_category_product_Alias" (cost=0.00..2.62 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1) |
| Filter: (id = 27) |
| Rows Removed by Filter: 129 |
| Buffers: shared hit=1 |
| -> Seq Scan on category (cost=0.00..2.62 rows=2 width=8) (actual time=0.008..0.011 rows=2 loops=1) |
| Filter: (parent = 27) |
| Rows Removed by Filter: 128 |
| Buffers: shared hit=1 |
| -> Sort (cost=22975.73..24333.73 rows=543198 width=4) (actual time=170.759..170.760 rows=11 loops=1) |
| Sort Key: cte_reviews.id DESC |
| Sort Method: top-N heapsort Memory: 26kB |
| Buffers: shared hit=681 read=6502 |
| -> CTE Scan on cte_reviews (cost=0.00..10863.96 rows=543198 width=4) (actual time=127.501..169.877 rows=5126 loops=1) |
| Buffers: shared hit=681 read=6502 |
| Planning time: 0.867 ms |
| Execution time: 170.847 ms |
EXPLAIN (ANALYZE, BUFFERS)
WITH
cte_reviews (id) AS (
SELECT r.id
FROM review AS r
INNER JOIN category_to_product AS cp ON (r.product = cp.product)
WHERE
cp.category IN (
SELECT 27
UNION ALL
SELECT id FROM category WHERE parent = 27
)
ORDER BY 1 ASC
)
SELECT id
FROM cte_reviews
ORDER BY id DESC
LIMIT 11 OFFSET 0;
| QUERY PLAN |
|---|
| Limit (cost=4546.95..4546.98 rows=11 width=4) (actual time=20.333..20.336 rows=11 loops=1) |
| Buffers: shared hit=4249 read=2355 |
| CTE cte_reviews |
| -> Sort (cost=3479.26..3538.84 rows=23834 width=4) (actual time=18.472..18.882 rows=5148 loops=1) |
| Sort Key: r.id |
| Sort Method: quicksort Memory: 434kB |
| Buffers: shared hit=4249 read=2355 |
| -> Nested Loop (cost=8.52..1746.44 rows=23834 width=4) (actual time=0.071..16.937 rows=5148 loops=1) |
| Buffers: shared hit=4249 read=2355 |
| -> Nested Loop (cost=8.09..219.32 rows=437 width=4) (actual time=0.056..1.549 rows=419 loops=1) |
| Buffers: shared hit=5 read=204 |
| -> HashAggregate (cost=2.67..2.70 rows=3 width=4) (actual time=0.022..0.026 rows=3 loops=1) |
| Group Key: (27) |
| Buffers: shared hit=1 |
| -> Append (cost=0.00..2.67 rows=3 width=4) (actual time=0.001..0.018 rows=3 loops=1) |
| Buffers: shared hit=1 |
| -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1) |
| -> Seq Scan on category (cost=0.00..2.62 rows=2 width=4) (actual time=0.013..0.017 rows=2 loops=1) |
| Filter: (parent = 27) |
| Rows Removed by Filter: 128 |
| Buffers: shared hit=1 |
| -> Bitmap Heap Scan on category_to_product cp (cost=5.42..70.75 rows=146 width=8) (actual time=0.031..0.473 rows=140 loops=3) |
| Recheck Cond: (category = (27)) |
| Heap Blocks: exact=202 |
| Buffers: shared hit=4 read=204 |
| -> Bitmap Index Scan on category_to_product_pkey (cost=0.00..5.38 rows=146 width=0) (actual time=0.021..0.021 rows=140 loops=3) |
| Index Cond: (category = (27)) |
| Buffers: shared read=6 |
| -> Index Scan using idx_review_product on review r (cost=0.42..2.93 rows=56 width=8) (actual time=0.009..0.034 rows=12 loops=419) |
| Index Cond: (product = cp.product) |
| Buffers: shared hit=4244 read=2151 |
| -> Sort (cost=1008.11..1067.70 rows=23834 width=4) (actual time=20.332..20.333 rows=11 loops=1) |
| Sort Key: cte_reviews.id DESC |
| Sort Method: top-N heapsort Memory: 26kB |
| Buffers: shared hit=4249 read=2355 |
| -> CTE Scan on cte_reviews (cost=0.00..476.68 rows=23834 width=4) (actual time=18.474..19.631 rows=5148 loops=1) |
| Buffers: shared hit=4249 read=2355 |
| Planning time: 0.583 ms |
| Execution time: 20.422 ms |