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?.
version |
---|
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
5 rows affected
version | col_a | col_b | col_c |
---|---|---|---|
1 | A1 | B1 | null |
2 | A2 | B3 | null |
3 | A3 | B2 | null |
4 | A4 | null | C1 |
5 | A5 | null | null |
col_a | col_b | col_c |
---|---|---|
A5 | B2 | C1 |
col_a | col_b | col_c |
---|---|---|
A5 | B2 | C1 |
lasta | lastb | lastc |
---|---|---|
A5 | B2 | C1 |
?column? |
---|
Performance Analysis (EXPLAIN (ANALYZE, TIMING OFF) below |
QUERY PLAN |
---|
Result (cost=0.72..0.73 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.15..0.24 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.15..57.60 rows=627 width=36) (actual rows=1 loops=1) |
Filter: (col_a IS NOT NULL) |
InitPlan 2 (returns $1) |
-> Limit (cost=0.15..0.24 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.15..57.60 rows=627 width=36) (actual rows=1 loops=1) |
Filter: (col_b IS NOT NULL) |
Rows Removed by Filter: 2 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.15..0.24 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.15..57.60 rows=627 width=36) (actual rows=1 loops=1) |
Filter: (col_c IS NOT NULL) |
Rows Removed by Filter: 1 |
Planning Time: 0.114 ms |
Execution Time: 0.030 ms |
QUERY PLAN |
---|
Nested Loop (cost=46.04..70.21 rows=1 width=96) (actual rows=1 loops=1) |
-> Nested Loop (cost=45.89..62.02 rows=1 width=68) (actual rows=1 loops=1) |
-> Nested Loop (cost=45.74..53.83 rows=1 width=40) (actual rows=1 loops=1) |
-> Limit (cost=45.59..45.63 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=45.59..66.07 rows=621 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 2 |
-> WindowAgg (cost=45.59..59.77 rows=630 width=112) (actual rows=3 loops=1) |
-> Sort (cost=45.59..47.17 rows=630 width=100) (actual rows=4 loops=1) |
Sort Key: tab.version DESC |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on tab (cost=0.00..16.30 rows=630 width=100) (actual rows=5 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.279 ms |
Execution Time: 0.174 ms |
QUERY PLAN |
---|
HashAggregate (cost=141.98..143.98 rows=200 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
-> WindowAgg (cost=126.23..137.25 rows=630 width=108) (actual rows=5 loops=1) |
-> Sort (cost=126.23..127.80 rows=630 width=172) (actual rows=5 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 25kB |
-> WindowAgg (cost=85.91..96.94 rows=630 width=172) (actual rows=5 loops=1) |
-> Sort (cost=85.91..87.49 rows=630 width=140) (actual rows=5 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 25kB |
-> WindowAgg (cost=45.59..56.62 rows=630 width=140) (actual rows=5 loops=1) |
-> Sort (cost=45.59..47.17 rows=630 width=108) (actual rows=5 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on tab (cost=0.00..16.30 rows=630 width=108) (actual rows=5 loops=1) |
Planning Time: 0.058 ms |
Execution Time: 0.073 ms |
19995 rows affected
QUERY PLAN |
---|
Result (cost=1.21..1.22 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.28..0.40 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.28..668.39 rows=5579 width=36) (actual rows=1 loops=1) |
Filter: (col_a IS NOT NULL) |
Rows Removed by Filter: 19995 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.28..0.40 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.28..668.39 rows=5579 width=36) (actual rows=1 loops=1) |
Filter: (col_b IS NOT NULL) |
Rows Removed by Filter: 19997 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.28..0.40 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.28..668.39 rows=5579 width=36) (actual rows=1 loops=1) |
Filter: (col_c IS NOT NULL) |
Rows Removed by Filter: 19996 |
Planning Time: 0.189 ms |
Execution Time: 8.558 ms |
QUERY PLAN |
---|
Nested Loop (cost=495.04..519.14 rows=1 width=96) (actual rows=1 loops=1) |
-> Nested Loop (cost=494.76..510.84 rows=1 width=68) (actual rows=1 loops=1) |
-> Nested Loop (cost=494.47..502.54 rows=1 width=40) (actual rows=1 loops=1) |
-> Limit (cost=494.19..494.22 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=494.19..676.42 rows=5523 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 19997 |
-> WindowAgg (cost=494.19..620.35 rows=5607 width=112) (actual rows=19998 loops=1) |
-> Sort (cost=494.19..508.21 rows=5607 width=100) (actual rows=19999 loops=1) |
Sort Key: tab.version DESC |
Sort Method: quicksort Memory: 1706kB |
-> Seq Scan on tab (cost=0.00..145.07 rows=5607 width=100) (actual rows=20000 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.245 ms |
Execution Time: 15.852 ms |
QUERY PLAN |
---|
HashAggregate (cost=1528.85..1534.46 rows=561 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
-> WindowAgg (cost=1388.68..1486.80 rows=5607 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=1388.68..1402.69 rows=5607 width=172) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=941.43..1039.56 rows=5607 width=172) (actual rows=20000 loops=1) |
-> Sort (cost=941.43..955.45 rows=5607 width=140) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> WindowAgg (cost=494.19..592.31 rows=5607 width=140) (actual rows=20000 loops=1) |
-> Sort (cost=494.19..508.21 rows=5607 width=108) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> Seq Scan on tab (cost=0.00..145.07 rows=5607 width=108) (actual rows=20000 loops=1) |
Planning Time: 0.077 ms |
Execution Time: 48.636 ms |
?column? |
---|
Adding partial indexes: (version) WHERE (column IS NOT NULL) |
QUERY PLAN |
---|
Result (cost=0.47..0.48 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.13..0.16 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using ix_va on tab (cost=0.13..563.16 rows=19900 width=36) (actual rows=1 loops=1) |
InitPlan 2 (returns $1) |
-> Limit (cost=0.13..0.16 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using ix_vb on tab tab_1 (cost=0.13..563.14 rows=19900 width=36) (actual rows=1 loops=1) |
InitPlan 3 (returns $2) |
-> Limit (cost=0.12..0.15 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Scan Backward using ix_vc on tab tab_2 (cost=0.12..559.13 rows=19900 width=36) (actual rows=1 loops=1) |
Planning Time: 0.625 ms |
Execution Time: 0.108 ms |
QUERY PLAN |
---|
Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) |
-> Limit (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.29..1484.29 rows=19701 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 19997 |
-> WindowAgg (cost=0.29..1284.29 rows=20000 width=112) (actual rows=19998 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..884.29 rows=20000 width=100) (actual rows=19999 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.417 ms |
Execution Time: 18.640 ms |
QUERY PLAN |
---|
HashAggregate (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=172) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=172) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=140) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=140) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=108) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=108) (actual rows=20000 loops=1) |
Planning Time: 0.088 ms |
Execution Time: 47.394 ms |
?column? |
---|
Adding partial indexex: (version) INCLUDE (column) WHERE (column IS NOT NULL) |
QUERY PLAN |
---|
Result (cost=0.47..0.48 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.13..0.16 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_a on tab (cost=0.13..563.16 rows=19900 width=36) (actual rows=1 loops=1) |
Heap Fetches: 1 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.13..0.16 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.13..563.14 rows=19900 width=36) (actual rows=1 loops=1) |
Heap Fetches: 1 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.12..0.15 rows=1 width=36) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.12..559.13 rows=19900 width=36) (actual rows=1 loops=1) |
Heap Fetches: 1 |
Planning Time: 0.466 ms |
Execution Time: 0.086 ms |
QUERY PLAN |
---|
Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) |
-> Limit (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.29..1484.29 rows=19701 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 19997 |
-> WindowAgg (cost=0.29..1284.29 rows=20000 width=112) (actual rows=19998 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..884.29 rows=20000 width=100) (actual rows=19999 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.304 ms |
Execution Time: 12.638 ms |
QUERY PLAN |
---|
HashAggregate (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=172) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=172) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=140) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=140) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=108) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1706kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=108) (actual rows=20000 loops=1) |
Planning Time: 0.113 ms |
Execution Time: 47.350 ms |