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 9.6.24 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit |
SELECT 1
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
INSERT 0 10
timestamp |
---|
2019-06-18 10:48:25 |
2019-06-18 10:53:17 |
2019-06-18 10:54:31 |
2019-06-18 11:03:59 |
2019-06-18 11:04:41 |
2019-06-18 11:11:04 |
2019-06-18 12:15:15 |
2019-06-18 13:04:41 |
2019-06-18 13:17:17 |
2019-06-18 14:05:17 |
SELECT 10
id | ctn | f_day | f_time | l_time |
---|---|---|---|---|
1 | 707 | 2019-06-18 | 10:48:25 | 10:56:17 |
3 | 707 | 2019-06-18 | 10:54:31 | 10:59:43 |
2 | 707 | 2019-06-18 | 10:53:17 | 11:00:49 |
5 | 707 | 2019-06-18 | 11:04:41 | 11:08:02 |
4 | 707 | 2019-06-18 | 11:03:59 | 11:10:39 |
6 | 707 | 2019-06-18 | 11:11:04 | 11:19:39 |
7 | 707 | 2019-06-18 | 12:15:15 | 13:13:13 |
8 | 707 | 2019-06-18 | 13:04:41 | 13:20:02 |
9 | 707 | 2019-06-18 | 13:17:17 | 13:22:22 |
10 | 707 | 2019-06-18 | 14:05:17 | 14:14:14 |
SELECT 10
id | ctn | ft | lt | ovl |
---|---|---|---|---|
1 | 707 | 10:48:25 | 10:56:17 | 1 |
2 | 707 | 10:53:17 | 11:00:49 | 0 |
3 | 707 | 10:54:31 | 10:59:43 | 0 |
4 | 707 | 11:03:59 | 11:10:39 | 1 |
5 | 707 | 11:04:41 | 11:08:02 | 0 |
6 | 707 | 11:11:04 | 11:19:39 | 1 |
7 | 707 | 12:15:15 | 13:13:13 | 1 |
8 | 707 | 13:04:41 | 13:20:02 | 0 |
9 | 707 | 13:17:17 | 13:22:22 | 0 |
10 | 707 | 14:05:17 | 14:14:14 | 1 |
SELECT 10
id | ctn | ft | lt | ovl | s |
---|---|---|---|---|---|
1 | 707 | 10:48:25 | 10:56:17 | 1 | 1 |
3 | 707 | 10:54:31 | 10:59:43 | 0 | 1 |
2 | 707 | 10:53:17 | 11:00:49 | 0 | 1 |
5 | 707 | 11:04:41 | 11:08:02 | 0 | 2 |
4 | 707 | 11:03:59 | 11:10:39 | 1 | 2 |
6 | 707 | 11:11:04 | 11:19:39 | 1 | 3 |
7 | 707 | 12:15:15 | 13:13:13 | 1 | 4 |
8 | 707 | 13:04:41 | 13:20:02 | 0 | 4 |
9 | 707 | 13:17:17 | 13:22:22 | 0 | 4 |
10 | 707 | 14:05:17 | 14:14:14 | 1 | 5 |
SELECT 10
rn | min_f | max_l | s |
---|---|---|---|
1 | 10:48:25 | 10:56:17 | 1 |
2 | 10:48:25 | 11:00:49 | 1 |
3 | 10:48:25 | 11:00:49 | 1 |
1 | 11:03:59 | 11:10:39 | 2 |
2 | 11:03:59 | 11:10:39 | 2 |
1 | 11:11:04 | 11:19:39 | 3 |
1 | 12:15:15 | 13:13:13 | 4 |
2 | 12:15:15 | 13:20:02 | 4 |
3 | 12:15:15 | 13:22:22 | 4 |
1 | 14:05:17 | 14:14:14 | 5 |
SELECT 10
rn | min_f | max_l | s |
---|---|---|---|
1 | 10:48:25 | 11:00:49 | 1 |
2 | 10:48:25 | 11:00:49 | 1 |
3 | 10:48:25 | 10:59:43 | 1 |
1 | 11:03:59 | 11:10:39 | 2 |
2 | 11:03:59 | 11:08:02 | 2 |
1 | 11:11:04 | 11:19:39 | 3 |
1 | 12:15:15 | 13:22:22 | 4 |
2 | 12:15:15 | 13:22:22 | 4 |
3 | 12:15:15 | 13:22:22 | 4 |
1 | 14:05:17 | 14:14:14 | 5 |
SELECT 10
Interval No. | Interval start | Interval stop | Duration | |||
---|---|---|---|---|---|---|
1 | Start time: | 10:48:25 | End time: | 11:00:49 | Duration: | 00:12:24 |
2 | Start time: | 11:03:59 | End time: | 11:10:39 | Duration: | 00:06:40 |
3 | Start time: | 11:11:04 | End time: | 11:19:39 | Duration: | 00:08:35 |
4 | Start time: | 12:15:15 | End time: | 13:22:22 | Duration: | 01:07:07 |
5 | Start time: | 14:05:17 | End time: | 14:14:14 | Duration: | 00:08:57 |
SELECT 5
ANALYZE
QUERY PLAN |
---|
WindowAgg (cost=3.27..3.44 rows=1 width=136) (actual time=0.491..0.500 rows=5 loops=1) |
Buffers: shared hit=1 |
-> Subquery Scan on t3 (cost=3.27..3.42 rows=1 width=16) (actual time=0.477..0.479 rows=5 loops=1) |
Filter: (t3.rn = 1) |
Rows Removed by Filter: 5 |
Buffers: shared hit=1 |
-> Sort (cost=3.27..3.30 rows=10 width=48) (actual time=0.476..0.476 rows=10 loops=1) |
Sort Key: t2.s, (row_number() OVER (?)), (min(t2.ft) OVER (?)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> WindowAgg (cost=2.88..3.11 rows=10 width=48) (actual time=0.397..0.414 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=2.88..2.91 rows=10 width=40) (actual time=0.394..0.397 rows=10 loops=1) |
Sort Key: t2.s, t2.ft DESC, t2.lt |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> WindowAgg (cost=2.49..2.71 rows=10 width=40) (actual time=0.326..0.345 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=2.49..2.51 rows=10 width=32) (actual time=0.322..0.323 rows=10 loops=1) |
Sort Key: t2.s, t2.ft, t2.lt DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> WindowAgg (cost=2.15..2.32 rows=10 width=32) (actual time=0.248..0.267 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=2.15..2.17 rows=10 width=24) (actual time=0.246..0.247 rows=10 loops=1) |
Sort Key: t2.s |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Subquery Scan on t2 (cost=1.86..1.98 rows=10 width=24) (actual time=0.189..0.193 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=1.86..1.88 rows=10 width=36) (actual time=0.189..0.190 rows=10 loops=1) |
Sort Key: t1.id, t1.lt |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> WindowAgg (cost=1.52..1.69 rows=10 width=36) (actual time=0.132..0.142 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=1.52..1.54 rows=10 width=24) (actual time=0.118..0.119 rows=10 loops=1) |
Sort Key: t1.ft |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Subquery Scan on t1 (cost=0.00..1.35 rows=10 width=24) (actual time=0.034..0.050 rows=10 loops=1) |
Buffers: shared hit=1 |
-> WindowAgg (cost=0.00..1.25 rows=10 width=28) (actual time=0.032..0.047 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Seq Scan on sessions s (cost=0.00..1.10 rows=10 width=20) (actual time=0.009..0.012 rows=10 loops=1) |
Buffers: shared hit=1 |
Planning time: 0.529 ms |
Execution time: 0.754 ms |
EXPLAIN
QUERY PLAN |
---|
WindowAgg (cost=3.32..3.36 rows=1 width=144) (actual time=0.826..0.834 rows=5 loops=1) |
Buffers: shared hit=2 |
CTE cte1 |
-> Sort (cost=3.05..3.05 rows=1 width=44) (actual time=0.224..0.225 rows=10 loops=1) |
Sort Key: s.l_time |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> Hash Join (cost=1.82..3.04 rows=1 width=44) (actual time=0.174..0.183 rows=10 loops=1) |
Hash Cond: ((s.ctn = t.ctn) AND (s.f_time = t.mt) AND (s.l_time = t.lt)) |
Buffers: shared hit=2 |
-> Seq Scan on sessions s (cost=0.00..1.10 rows=10 width=28) (actual time=0.009..0.010 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Hash (cost=1.64..1.64 rows=10 width=20) (actual time=0.115..0.115 rows=10 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=1 |
-> Subquery Scan on t (cost=1.44..1.64 rows=10 width=20) (actual time=0.069..0.078 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Unique (cost=1.44..1.54 rows=10 width=20) (actual time=0.069..0.077 rows=10 loops=1) |
Buffers: shared hit=1 |
-> Sort (cost=1.44..1.47 rows=10 width=20) (actual time=0.068..0.069 rows=10 loops=1) |
Sort Key: sessions.l_time, sessions.ctn, (min(sessions.f_time)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> HashAggregate (cost=1.18..1.28 rows=10 width=20) (actual time=0.016..0.018 rows=10 loops=1) |
Group Key: sessions.l_time, sessions.ctn |
Buffers: shared hit=1 |
-> Seq Scan on sessions (cost=0.00..1.10 rows=10 width=20) (actual time=0.004..0.006 rows=10 loops=1) |
Buffers: shared hit=1 |
CTE cte2 |
-> Unique (cost=0.05..0.06 rows=1 width=20) (actual time=0.306..0.310 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.05..0.05 rows=1 width=20) (actual time=0.306..0.306 rows=10 loops=1) |
Sort Key: cte1.f_time, cte1.ctn, (max(cte1.lt)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> HashAggregate (cost=0.03..0.04 rows=1 width=20) (actual time=0.245..0.245 rows=10 loops=1) |
Group Key: cte1.f_time, cte1.ctn |
Buffers: shared hit=2 |
-> CTE Scan on cte1 (cost=0.00..0.02 rows=1 width=20) (actual time=0.225..0.229 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Subquery Scan on t3 (cost=0.21..0.23 rows=1 width=24) (actual time=0.815..0.818 rows=5 loops=1) |
Filter: (t3.rn = 1) |
Rows Removed by Filter: 5 |
Buffers: shared hit=2 |
-> Sort (cost=0.21..0.22 rows=1 width=48) (actual time=0.813..0.813 rows=10 loops=1) |
Sort Key: t2.s, (row_number() OVER (?)), (min(t2.ft) OVER (?)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> WindowAgg (cost=0.18..0.20 rows=1 width=48) (actual time=0.729..0.748 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.18..0.18 rows=1 width=40) (actual time=0.727..0.728 rows=10 loops=1) |
Sort Key: t2.s, t2.ft DESC, t2.lt |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> WindowAgg (cost=0.14..0.17 rows=1 width=40) (actual time=0.659..0.679 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.14..0.15 rows=1 width=32) (actual time=0.655..0.655 rows=10 loops=1) |
Sort Key: t2.s, t2.ft, t2.lt DESC |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> WindowAgg (cost=0.11..0.13 rows=1 width=32) (actual time=0.593..0.611 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.11..0.12 rows=1 width=24) (actual time=0.591..0.594 rows=10 loops=1) |
Sort Key: t2.s |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> Subquery Scan on t2 (cost=0.09..0.10 rows=1 width=24) (actual time=0.531..0.535 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.09..0.09 rows=1 width=32) (actual time=0.530..0.531 rows=10 loops=1) |
Sort Key: t1.lt |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> WindowAgg (cost=0.06..0.07 rows=1 width=32) (actual time=0.419..0.433 rows=10 loops=1) |
Buffers: shared hit=2 |
-> Sort (cost=0.06..0.06 rows=1 width=20) (actual time=0.405..0.407 rows=10 loops=1) |
Sort Key: t1.ft |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> Subquery Scan on t1 (cost=0.00..0.05 rows=1 width=20) (actual time=0.320..0.347 rows=10 loops=1) |
Buffers: shared hit=2 |
-> WindowAgg (cost=0.00..0.04 rows=1 width=24) (actual time=0.319..0.344 rows=10 loops=1) |
Buffers: shared hit=2 |
-> CTE Scan on cte2 c (cost=0.00..0.02 rows=1 width=16) (actual time=0.308..0.315 rows=10 loops=1) |
Buffers: shared hit=2 |
Planning time: 2.777 ms |
Execution time: 1.554 ms |
EXPLAIN