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.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit |
14 rows affected
the_date |
---|
2021-06-23 05:47:05+01 |
2021-06-23 07:47:05+01 |
2021-06-23 09:47:05+01 |
2021-06-23 11:47:05+01 |
2021-06-23 13:47:05+01 |
2021-06-23 14:47:05+01 |
2021-06-23 16:47:05+01 |
2021-06-23 17:47:05+01 |
2021-06-24 05:47:05+01 |
2021-06-24 09:47:05+01 |
2021-06-24 13:47:05+01 |
2021-06-24 14:47:05+01 |
2021-06-24 16:47:05+01 |
2021-06-24 17:47:05+01 |
dat | td | l_td | diff |
---|---|---|---|
2021-06-23 | 2021-06-23 05:47:05+01 | 2021-06-23 07:47:05+01 | 02:00:00 |
2021-06-23 | 2021-06-23 07:47:05+01 | 2021-06-23 09:47:05+01 | 02:00:00 |
2021-06-23 | 2021-06-23 09:47:05+01 | 2021-06-23 11:47:05+01 | 02:00:00 |
2021-06-23 | 2021-06-23 11:47:05+01 | 2021-06-23 13:47:05+01 | 02:00:00 |
2021-06-23 | 2021-06-23 13:47:05+01 | 2021-06-23 14:47:05+01 | 01:00:00 |
2021-06-23 | 2021-06-23 14:47:05+01 | 2021-06-23 16:47:05+01 | 02:00:00 |
2021-06-23 | 2021-06-23 16:47:05+01 | 2021-06-23 17:47:05+01 | 01:00:00 |
2021-06-23 | 2021-06-23 17:47:05+01 | null | null |
2021-06-24 | 2021-06-24 05:47:05+01 | 2021-06-24 09:47:05+01 | 04:00:00 |
2021-06-24 | 2021-06-24 09:47:05+01 | 2021-06-24 13:47:05+01 | 04:00:00 |
2021-06-24 | 2021-06-24 13:47:05+01 | 2021-06-24 14:47:05+01 | 01:00:00 |
2021-06-24 | 2021-06-24 14:47:05+01 | 2021-06-24 16:47:05+01 | 02:00:00 |
2021-06-24 | 2021-06-24 16:47:05+01 | 2021-06-24 17:47:05+01 | 01:00:00 |
2021-06-24 | 2021-06-24 17:47:05+01 | null | null |
dat | td | diff |
---|---|---|
2021-06-24 | 2021-06-24 05:47:05+01 | 04:00:00 |
2021-06-24 | 2021-06-24 09:47:05+01 | 04:00:00 |
dat | min |
---|---|
2021-06-23 | 2021-06-23 17:47:05+01 |
2021-06-24 | 2021-06-24 05:47:05+01 |
The date | Gap start or last ts |
---|---|
2021-06-23 | 2021-06-23 17:47:05+01 |
2021-06-24 | 2021-06-24 05:47:05+01 |
The date | Gap start or last ts |
---|---|
2021-06-23 | 2021-06-23 17:47:05+01 |
2021-06-24 | 2021-06-24 05:47:05+01 |
dat | td |
---|---|
2021-06-23 | 2021-06-23 17:47:05+01 |
2021-06-24 | 2021-06-24 05:47:05+01 |
QUERY PLAN |
---|
Sort (cost=200.56..201.56 rows=398 width=12) (actual time=0.170..0.172 rows=2 loops=1) |
Output: long_gaps.dat, long_gaps.gap |
Sort Key: long_gaps.dat |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
CTE long_gaps |
-> HashAggregate (cost=104.16..106.14 rows=198 width=12) (actual time=0.112..0.113 rows=2 loops=1) |
Output: t1.dat, min(t1.td) |
Group Key: t1.dat |
Buffers: shared hit=1 |
-> Subquery Scan on t1 (cost=0.00..100.40 rows=753 width=12) (actual time=0.099..0.107 rows=3 loops=1) |
Output: t1.dat, t1.td, t1.l_td |
Filter: ((t1.l_td - t1.td) > '03:00:00'::interval) |
Rows Removed by Filter: 11 |
Buffers: shared hit=1 |
-> WindowAgg (cost=0.00..66.50 rows=2260 width=20) (actual time=0.089..0.101 rows=14 loops=1) |
Output: (test_1.the_date)::date, test_1.the_date, lead(test_1.the_date) OVER (?) |
Buffers: shared hit=1 |
-> Seq Scan on public.test test_1 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.008..0.010 rows=14 loops=1) |
Output: test_1.the_date |
Buffers: shared hit=1 |
-> HashAggregate (cost=73.25..77.23 rows=398 width=12) (actual time=0.160..0.161 rows=2 loops=1) |
Output: long_gaps.dat, long_gaps.gap |
Group Key: long_gaps.dat, long_gaps.gap |
Buffers: shared hit=2 |
-> Append (cost=0.00..71.26 rows=398 width=12) (actual time=0.114..0.156 rows=2 loops=1) |
Buffers: shared hit=2 |
-> CTE Scan on long_gaps (cost=0.00..3.96 rows=198 width=12) (actual time=0.113..0.115 rows=2 loops=1) |
Output: long_gaps.dat, long_gaps.gap |
Buffers: shared hit=1 |
-> HashAggregate (cost=56.83..59.33 rows=200 width=12) (actual time=0.040..0.040 rows=0 loops=1) |
Output: ((test.the_date)::date), max(test.the_date) |
Group Key: (test.the_date)::date |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=4.46..51.18 rows=1130 width=12) (actual time=0.038..0.038 rows=0 loops=1) |
Output: (test.the_date)::date, test.the_date |
Filter: (NOT (hashed SubPlan 2)) |
Rows Removed by Filter: 14 |
Buffers: shared hit=1 |
SubPlan 2 |
-> CTE Scan on long_gaps long_gaps_1 (cost=0.00..3.96 rows=198 width=4) (actual time=0.001..0.001 rows=2 loops=1) |
Output: long_gaps_1.dat |
Planning Time: 0.223 ms |
Execution Time: 0.264 ms |
QUERY PLAN |
---|
Unique (cost=279.68..283.48 rows=198 width=12) (actual time=0.041..0.044 rows=2 loops=1) |
Output: long_gaps.dat, long_gaps.td |
Buffers: shared hit=1 |
-> Sort (cost=279.68..281.58 rows=761 width=12) (actual time=0.041..0.042 rows=4 loops=1) |
Output: long_gaps.dat, long_gaps.td |
Sort Key: long_gaps.dat, long_gaps.td |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Subquery Scan on long_gaps (cost=164.16..243.26 rows=761 width=12) (actual time=0.031..0.038 rows=4 loops=1) |
Output: long_gaps.dat, long_gaps.td |
Filter: (((long_gaps.l_td - long_gaps.td) > '03:00:00'::interval) OR (long_gaps.l_td IS NULL)) |
Rows Removed by Filter: 10 |
Buffers: shared hit=1 |
-> WindowAgg (cost=164.16..209.36 rows=2260 width=20) (actual time=0.024..0.033 rows=14 loops=1) |
Output: ((test.the_date)::date), test.the_date, lead(test.the_date) OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=164.16..169.81 rows=2260 width=12) (actual time=0.019..0.021 rows=14 loops=1) |
Output: ((test.the_date)::date), test.the_date |
Sort Key: ((test.the_date)::date) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=0.00..38.25 rows=2260 width=12) (actual time=0.011..0.014 rows=14 loops=1) |
Output: (test.the_date)::date, test.the_date |
Buffers: shared hit=1 |
Planning Time: 0.066 ms |
Execution Time: 0.069 ms |
QUERY PLAN |
---|
Subquery Scan on long_gaps (cost=279.68..304.41 rows=4 width=12) (actual time=0.051..0.056 rows=2 loops=1) |
Output: long_gaps.dat, long_gaps.td |
Filter: (long_gaps.rn = 1) |
Rows Removed by Filter: 2 |
Buffers: shared hit=1 |
-> WindowAgg (cost=279.68..294.90 rows=761 width=20) (actual time=0.050..0.054 rows=4 loops=1) |
Output: gaps.dat, gaps.td, row_number() OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=279.68..281.58 rows=761 width=12) (actual time=0.047..0.048 rows=4 loops=1) |
Output: gaps.dat, gaps.td |
Sort Key: gaps.dat, gaps.td |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Subquery Scan on gaps (cost=164.16..243.26 rows=761 width=12) (actual time=0.037..0.044 rows=4 loops=1) |
Output: gaps.dat, gaps.td |
Filter: (((gaps.l_td - gaps.td) > '03:00:00'::interval) OR (gaps.l_td IS NULL)) |
Rows Removed by Filter: 10 |
Buffers: shared hit=1 |
-> WindowAgg (cost=164.16..209.36 rows=2260 width=20) (actual time=0.028..0.039 rows=14 loops=1) |
Output: ((test.the_date)::date), test.the_date, lead(test.the_date) OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=164.16..169.81 rows=2260 width=12) (actual time=0.023..0.025 rows=14 loops=1) |
Output: ((test.the_date)::date), test.the_date |
Sort Key: ((test.the_date)::date) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=0.00..38.25 rows=2260 width=12) (actual time=0.013..0.018 rows=14 loops=1) |
Output: (test.the_date)::date, test.the_date |
Buffers: shared hit=1 |
Planning Time: 0.106 ms |
Execution Time: 0.086 ms |