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.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit |
SELECT 1
CREATE TABLE
INSERT 0 5
row_number | code | from_date | to_date |
---|---|---|---|
1 | A | 2021-09-07 | null |
2 | A | 2021-04-01 | 2021-09-06 |
3 | B | 2021-03-13 | 2021-03-31 |
4 | A | 2021-01-13 | 2021-03-12 |
5 | A | 2021-01-01 | 2021-01-12 |
SELECT 5
rn | code | from_date | to_date | code_change |
---|---|---|---|---|
1 | A | 2021-09-07 | null | 1 |
2 | A | 2021-04-01 | 2021-09-06 | 0 |
3 | B | 2021-03-13 | 2021-03-31 | 1 |
4 | A | 2021-01-13 | 2021-03-12 | 1 |
5 | A | 2021-01-01 | 2021-01-12 | 0 |
SELECT 5
rn | code | from_date | to_date | code_change | sum |
---|---|---|---|---|---|
1 | A | 2021-09-07 | null | 1 | 1 |
2 | A | 2021-04-01 | 2021-09-06 | 0 | 1 |
3 | B | 2021-03-13 | 2021-03-31 | 1 | 2 |
4 | A | 2021-01-13 | 2021-03-12 | 1 | 3 |
5 | A | 2021-01-01 | 2021-01-12 | 0 | 3 |
SELECT 5
code | min |
---|---|
A | 1 |
SELECT 1
rn | code | from_date | to_date | code_change | row_number | code | from_date | to_date | sum |
---|---|---|---|---|---|---|---|---|---|
1 | A | 2021-09-07 | null | 1 | 1 | A | 2021-09-07 | null | 1 |
2 | A | 2021-04-01 | 2021-09-06 | 0 | 2 | A | 2021-04-01 | 2021-09-06 | 1 |
3 | B | 2021-03-13 | 2021-03-31 | 1 | 3 | B | 2021-03-13 | 2021-03-31 | 2 |
4 | A | 2021-01-13 | 2021-03-12 | 1 | 4 | A | 2021-01-13 | 2021-03-12 | 3 |
5 | A | 2021-01-01 | 2021-01-12 | 0 | 5 | A | 2021-01-01 | 2021-01-12 | 3 |
SELECT 5
rn | code | from_date | to_date | code_change | xx | code | yy |
---|---|---|---|---|---|---|---|
1 | A | 2021-09-07 | null | 1 | 1 | A | 1 |
2 | A | 2021-04-01 | 2021-09-06 | 0 | 1 | A | 1 |
SELECT 2
row_number | code | from_date | to_date | next_from |
---|---|---|---|---|
1 | A | 2021-09-07 | null | null |
2 | A | 2021-04-01 | 2021-09-06 | 2021-09-07 |
4 | A | 2021-01-13 | 2021-03-12 | 2021-04-01 |
5 | A | 2021-01-01 | 2021-01-12 | 2021-01-13 |
SELECT 4
row_number | code | from_date | to_date |
---|---|---|---|
1 | A | 2021-09-07 | null |
2 | A | 2021-04-01 | 2021-09-06 |
SELECT 2
QUERY PLAN |
---|
Hash Join (cost=222.41..291.96 rows=34 width=100) (actual time=0.123..0.135 rows=2 loops=1) |
Output: tab_01.rn, tab_01.code, tab_01.from_date, tab_01.to_date, tab_01.code_change, (sum(tab_01.code_change) OVER (?)), tab_02.code, (min(tab_02.sum_cc)) |
Hash Cond: ((sum(tab_01.code_change) OVER (?)) = (min(tab_02.sum_cc))) |
Buffers: shared hit=2 |
-> WindowAgg (cost=78.60..132.28 rows=1130 width=60) (actual time=0.041..0.050 rows=5 loops=1) |
Output: tab_01.rn, tab_01.code, tab_01.from_date, tab_01.to_date, tab_01.code_change, sum(tab_01.code_change) OVER (?) |
Buffers: shared hit=1 |
-> Subquery Scan on tab_01 (cost=78.60..115.33 rows=1130 width=52) (actual time=0.033..0.041 rows=5 loops=1) |
Output: tab_01.from_date, tab_01.rn, tab_01.code, tab_01.to_date, tab_01.code_change |
Buffers: shared hit=1 |
-> WindowAgg (cost=78.60..104.03 rows=1130 width=52) (actual time=0.032..0.039 rows=5 loops=1) |
Output: row_number() OVER (?), test.code, test.from_date, test.to_date, CASE WHEN (lag(test.code) OVER (?) = test.code) THEN 0 ELSE 1 END |
Buffers: shared hit=1 |
-> Sort (cost=78.60..81.43 rows=1130 width=40) (actual time=0.017..0.018 rows=5 loops=1) |
Output: test.from_date, test.code, test.to_date |
Sort Key: test.from_date DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=0.00..21.30 rows=1130 width=40) (actual time=0.010..0.011 rows=5 loops=1) |
Output: test.from_date, test.code, test.to_date |
Buffers: shared hit=1 |
-> Hash (cost=143.73..143.73 rows=6 width=40) (actual time=0.055..0.056 rows=1 loops=1) |
Output: tab_02.code, (min(tab_02.sum_cc)) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=1 |
-> GroupAggregate (cost=78.60..143.67 rows=6 width=40) (actual time=0.054..0.055 rows=1 loops=1) |
Output: tab_02.code, min(tab_02.sum_cc) |
Group Key: tab_02.code |
Buffers: shared hit=1 |
-> Subquery Scan on tab_02 (cost=78.60..143.58 rows=6 width=40) (actual time=0.040..0.049 rows=4 loops=1) |
Output: tab_02.rn, tab_02.code, tab_02.from_date, tab_02.to_date, tab_02.code_change, tab_02.sum_cc |
Filter: (tab_02.code = 'A'::text) |
Rows Removed by Filter: 1 |
Buffers: shared hit=1 |
-> WindowAgg (cost=78.60..129.45 rows=1130 width=60) (actual time=0.039..0.047 rows=5 loops=1) |
Output: NULL::bigint, tab_01_1.code, tab_01_1.from_date, NULL::date, NULL::integer, sum(tab_01_1.code_change) OVER (?) |
Buffers: shared hit=1 |
-> Subquery Scan on tab_01_1 (cost=78.60..112.50 rows=1130 width=40) (actual time=0.025..0.032 rows=5 loops=1) |
Output: tab_01_1.from_date, tab_01_1.code, tab_01_1.code_change |
Buffers: shared hit=1 |
-> WindowAgg (cost=78.60..101.20 rows=1130 width=52) (actual time=0.025..0.031 rows=5 loops=1) |
Output: NULL::bigint, test_1.code, test_1.from_date, NULL::date, CASE WHEN (lag(test_1.code) OVER (?) = test_1.code) THEN 0 ELSE 1 END |
Buffers: shared hit=1 |
-> Sort (cost=78.60..81.43 rows=1130 width=36) (actual time=0.016..0.017 rows=5 loops=1) |
Output: test_1.from_date, test_1.code |
Sort Key: test_1.from_date DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test test_1 (cost=0.00..21.30 rows=1130 width=36) (actual time=0.007..0.008 rows=5 loops=1) |
Output: test_1.from_date, test_1.code |
Buffers: shared hit=1 |
Planning Time: 0.393 ms |
Execution Time: 0.600 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=24.85..24.86 rows=1 width=44) (actual time=0.422..0.423 rows=2 loops=1) |
Output: group_enumerate.row_number, group_enumerate.code, group_enumerate.from_date, group_enumerate.to_date |
Sort Key: group_enumerate.row_number |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
CTE group_enumerate |
-> WindowAgg (cost=24.20..24.56 rows=6 width=60) (actual time=0.395..0.406 rows=4 loops=1) |
Output: test.row_number, test.code, test.from_date, test.to_date, (lag(test.from_date) OVER (?)), CASE WHEN ((test.row_number = 1) OR (((lag(test.from_date) OVER (?)) - test.to_date) = 1)) THEN 1 ELSE 0 END, (test.row_number - sum(CASE WHEN ((test.row_number = 1) OR (((lag(test.from_date) OVER (?)) - test.to_date) = 1)) THEN 1 ELSE 0 END) OVER (?)) |
Buffers: shared hit=1 |
-> WindowAgg (cost=24.20..24.31 rows=6 width=48) (actual time=0.031..0.038 rows=4 loops=1) |
Output: test.row_number, test.code, test.from_date, test.to_date, lag(test.from_date) OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=24.20..24.22 rows=6 width=44) (actual time=0.016..0.018 rows=4 loops=1) |
Output: test.row_number, test.code, test.from_date, test.to_date |
Sort Key: test.row_number |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=0.00..24.12 rows=6 width=44) (actual time=0.012..0.013 rows=4 loops=1) |
Output: test.row_number, test.code, test.from_date, test.to_date |
Filter: (test.code = 'A'::text) |
Rows Removed by Filter: 1 |
Buffers: shared hit=1 |
InitPlan 2 (returns $1) |
-> Aggregate (cost=0.14..0.15 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1) |
Output: min(group_enumerate_1.grpn) |
-> CTE Scan on group_enumerate group_enumerate_1 (cost=0.00..0.12 rows=6 width=8) (actual time=0.001..0.013 rows=4 loops=1) |
Output: group_enumerate_1.row_number, group_enumerate_1.code, group_enumerate_1.from_date, group_enumerate_1.to_date, group_enumerate_1.next_from, group_enumerate_1.group_cnt, group_enumerate_1.grpn |
-> CTE Scan on group_enumerate (cost=0.00..0.14 rows=1 width=44) (actual time=0.418..0.419 rows=2 loops=1) |
Output: group_enumerate.row_number, group_enumerate.code, group_enumerate.from_date, group_enumerate.to_date |
Filter: (group_enumerate.grpn = $1) |
Rows Removed by Filter: 2 |
Buffers: shared hit=1 |
Planning Time: 0.157 ms |
Execution Time: 0.471 ms |
EXPLAIN