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 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE
INSERT 0 1
CREATE TABLE
CREATE INDEX
INSERT 0 1
CREATE TABLE
INSERT 0 21700
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 20
datran | ms | sensor_id |
---|---|---|
["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00") | stationary | 5 |
["2022-01-01 00:20:00+00","2022-01-01 00:40:00+00") | in_motion) | 5 |
["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00") | in_motion) | 5 |
["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00") | in_motion) | 5 |
["2022-01-01 01:20:00+00","2022-01-01 01:40:00+00") | stationary | 5 |
["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00") | stationary | 5 |
["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00") | in_motion) | 5 |
["2022-01-01 02:20:00+00","2022-01-01 02:40:00+00") | in_motion) | 5 |
["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00") | in_motion) | 5 |
["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00") | stationary | 5 |
SELECT 10
CREATE TABLE
CREATE INDEX
INSERT 0 15
shift_id | location_id | occurence_id | datran |
---|---|---|---|
Shift A | 16 | 1 | ["2022-01-01 00:00:00+00","2022-01-01 00:30:00+00") |
Shift B | 16 | 2 | ["2022-01-01 00:30:00+00","2022-01-01 01:00:00+00") |
Shift A | 16 | 3 | ["2022-01-01 01:00:00+00","2022-01-01 01:30:00+00") |
Shift B | 16 | 4 | ["2022-01-01 01:30:00+00","2022-01-01 02:00:00+00") |
Shift A | 16 | 5 | ["2022-01-01 02:00:00+00","2022-01-01 02:30:00+00") |
SELECT 5
rn | mod | range_date | occurence_id |
---|---|---|---|
1 | 1 | ["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00") | 1 |
2 | 2 | ["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00") | 1 |
3 | 3 | ["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00") | 2 |
4 | 0 | ["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00") | 2 |
5 | 1 | ["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00") | 3 |
6 | 2 | ["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00") | 3 |
7 | 3 | ["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00") | 4 |
8 | 0 | ["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00") | 4 |
9 | 1 | ["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00") | 5 |
10 | 2 | ["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00") | 5 |
11 | 3 | ["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00") | 6 |
12 | 0 | ["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00") | 6 |
13 | 1 | ["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00") | 7 |
14 | 2 | ["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00") | 7 |
15 | 3 | ["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00") | 8 |
16 | 0 | ["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00") | 8 |
17 | 1 | ["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00") | 9 |
18 | 2 | ["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00") | 9 |
19 | 3 | ["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00") | 10 |
20 | 0 | ["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00") | 10 |
21 | 1 | ["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00") | 11 |
22 | 2 | ["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00") | 11 |
23 | 3 | ["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00") | 12 |
24 | 0 | ["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00") | 12 |
25 | 1 | ["2022-01-01 06:00:00+00","2022-01-01 06:20:00+00") | 13 |
26 | 2 | ["2022-01-01 06:20:00+00","2022-01-01 06:30:00+00") | 13 |
27 | 3 | ["2022-01-01 06:30:00+00","2022-01-01 06:40:00+00") | 14 |
SELECT 27
s_id | loc_id | S_val | E_val | range_date | ms | oc_id |
---|---|---|---|---|---|---|
5 | 16 | 1 | 1200 | ["2022-01-01 00:00:00+00","2022-01-01 00:20:00+00") | stationary | 1 |
5 | 16 | 1201 | 1800 | ["2022-01-01 00:20:00+00","2022-01-01 00:30:00+00") | in_motion) | 1 |
5 | 16 | 1801 | 2400 | ["2022-01-01 00:30:00+00","2022-01-01 00:40:00+00") | in_motion) | 2 |
5 | 16 | 2401 | 3600 | ["2022-01-01 00:40:00+00","2022-01-01 01:00:00+00") | in_motion) | 2 |
5 | 16 | 3601 | 4800 | ["2022-01-01 01:00:00+00","2022-01-01 01:20:00+00") | in_motion) | 3 |
5 | 16 | 4801 | 5400 | ["2022-01-01 01:20:00+00","2022-01-01 01:30:00+00") | stationary | 3 |
5 | 16 | 5401 | 6000 | ["2022-01-01 01:30:00+00","2022-01-01 01:40:00+00") | stationary | 4 |
5 | 16 | 6001 | 7200 | ["2022-01-01 01:40:00+00","2022-01-01 02:00:00+00") | stationary | 4 |
5 | 16 | 7201 | 8400 | ["2022-01-01 02:00:00+00","2022-01-01 02:20:00+00") | in_motion) | 5 |
5 | 16 | 8401 | 9000 | ["2022-01-01 02:20:00+00","2022-01-01 02:30:00+00") | in_motion) | 5 |
5 | 16 | 9001 | 9600 | ["2022-01-01 02:30:00+00","2022-01-01 02:40:00+00") | in_motion) | 6 |
5 | 16 | 9601 | 10800 | ["2022-01-01 02:40:00+00","2022-01-01 03:00:00+00") | in_motion) | 6 |
5 | 16 | 10801 | 12000 | ["2022-01-01 03:00:00+00","2022-01-01 03:20:00+00") | stationary | 7 |
5 | 16 | 12001 | 12600 | ["2022-01-01 03:20:00+00","2022-01-01 03:30:00+00") | stationary | 7 |
5 | 16 | 12601 | 13200 | ["2022-01-01 03:30:00+00","2022-01-01 03:40:00+00") | stationary | 8 |
5 | 16 | 13201 | 14400 | ["2022-01-01 03:40:00+00","2022-01-01 04:00:00+00") | in_motion) | 8 |
5 | 16 | 14401 | 15600 | ["2022-01-01 04:00:00+00","2022-01-01 04:20:00+00") | in_motion) | 9 |
5 | 16 | 15601 | 16200 | ["2022-01-01 04:20:00+00","2022-01-01 04:30:00+00") | in_motion) | 9 |
5 | 16 | 16201 | 16800 | ["2022-01-01 04:30:00+00","2022-01-01 04:40:00+00") | in_motion) | 10 |
5 | 16 | 16801 | 18000 | ["2022-01-01 04:40:00+00","2022-01-01 05:00:00+00") | in_motion) | 10 |
5 | 16 | 18001 | 19200 | ["2022-01-01 05:00:00+00","2022-01-01 05:20:00+00") | in_motion) | 11 |
5 | 16 | 19201 | 19800 | ["2022-01-01 05:20:00+00","2022-01-01 05:30:00+00") | in_motion) | 11 |
5 | 16 | 19801 | 20400 | ["2022-01-01 05:30:00+00","2022-01-01 05:40:00+00") | in_motion) | 12 |
5 | 16 | 20401 | 21600 | ["2022-01-01 05:40:00+00","2022-01-01 06:00:00+00") | stationary | 12 |
SELECT 24
SET
SET
QUERY PLAN |
---|
Sort (cost=10000003301.88..10000003302.43 rows=217 width=80) (actual time=263.933..263.941 rows=24 loops=1) |
Output: t.sensor_id, sub.location_id, sub.t_val, t.t_val, sub.range_date, sub.ms, sub.occurence_id |
Sort Key: sub.range_date |
Sort Method: quicksort Memory: 28kB |
Buffers: shared hit=3083 read=36 |
-> Hash Join (cost=10000002110.17..10000003293.46 rows=217 width=80) (actual time=247.168..263.909 rows=24 loops=1) |
Output: t.sensor_id, sub.location_id, sub.t_val, t.t_val, sub.range_date, sub.ms, sub.occurence_id |
Hash Cond: (t.t_ts = (upper(sub.range_date) - '00:00:01'::interval)) |
Buffers: shared hit=3083 read=36 |
-> Index Scan using telemetry_t_ix on public.telemetry t (cost=0.29..1045.79 rows=21700 width=16) (actual time=0.031..14.888 rows=21700 loops=1) |
Output: t.t_ts, t.sensor_id, t.t_val |
Buffers: shared hit=143 read=36 |
-> Hash (cost=10000002109.85..10000002109.85 rows=2 width=72) (actual time=246.747..246.752 rows=25 loops=1) |
Output: sub.location_id, sub.t_val, sub.range_date, sub.ms, sub.occurence_id |
Buckets: 1024 Batches: 1 Memory Usage: 10kB |
Buffers: shared hit=2940 |
-> Subquery Scan on sub (cost=10000001486.87..10000002109.85 rows=2 width=72) (actual time=246.692..246.732 rows=25 loops=1) |
Output: sub.location_id, sub.t_val, sub.range_date, sub.ms, sub.occurence_id |
Buffers: shared hit=2940 |
-> WindowAgg (cost=10000001486.87..10000002109.83 rows=2 width=144) (actual time=246.690..246.726 rows=25 loops=1) |
Output: NULL::integer, t_1.t_val, sh.occurence_id, st.ms, sh.location_id, CASE (row_number() OVER (?) % '4'::bigint) WHEN 1 THEN st.datran WHEN 2 THEN tstzrange(lower(st.datran), upper(sh.datran)) WHEN 3 THEN tstzrange(lower(sh.datran), upper(st.datran)) WHEN 0 THEN st.datran ELSE NULL::tstzrange END, st.datran, sh.datran, s.sensor_id, s.location_id |
Buffers: shared hit=2940 |
-> Incremental Sort (cost=10000001486.87..10000002109.73 rows=2 width=108) (actual time=246.637..246.644 rows=25 loops=1) |
Output: st.datran, sh.datran, s.sensor_id, s.location_id, t_1.t_val, sh.occurence_id, st.ms, sh.location_id |
Sort Key: s.sensor_id, s.location_id, st.datran, sh.datran |
Presorted Key: s.sensor_id, s.location_id |
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 28kB Peak Memory: 28kB |
Buffers: shared hit=2940 |
-> Merge Join (cost=10000000864.07..10000002109.64 rows=1 width=108) (actual time=0.209..246.555 rows=25 loops=1) |
Output: st.datran, sh.datran, s.sensor_id, s.location_id, t_1.t_val, sh.occurence_id, st.ms, sh.location_id |
Inner Unique: true |
Merge Cond: (s.sensor_id = t_1.sensor_id) |
Join Filter: (GREATEST(lower(st.datran), lower(sh.datran)) = t_1.t_ts) |
Rows Removed by Join Filter: 313400 |
Buffers: shared hit=2940 |
-> Merge Join (cost=10000000863.78..10000000910.50 rows=18 width=106) (actual time=0.188..0.254 rows=27 loops=1) |
Output: s.sensor_id, s.location_id, st.ms, st.datran, st.sensor_id, sh.occurence_id, sh.location_id, sh.datran |
Inner Unique: true |
Merge Cond: ((st.sensor_id = s.sensor_id) AND (sh.location_id = s.location_id)) |
Buffers: shared hit=44 |
-> Sort (cost=10000000633.10..10000000642.23 rows=3655 width=102) (actual time=0.161..0.185 rows=27 loops=1) |
Output: st.ms, st.datran, st.sensor_id, sh.occurence_id, sh.location_id, sh.datran |
Sort Key: st.sensor_id, sh.location_id |
Sort Method: quicksort Memory: 28kB |
Buffers: shared hit=42 |
-> Nested Loop (cost=10000000000.29..10000000416.80 rows=3655 width=102) (actual time=0.054..0.134 rows=27 loops=1) |
Output: st.ms, st.datran, st.sensor_id, sh.occurence_id, sh.location_id, sh.datran |
Buffers: shared hit=42 |
-> Index Scan using status_s_ix on public.status st (cost=0.15..61.05 rows=860 width=66) (actual time=0.013..0.018 rows=20 loops=1) |
Output: st.datran, st.ms, st.sensor_id |
Buffers: shared hit=2 |
-> Index Scan using shift_d_ix on public.shift sh (cost=0.14..0.33 rows=8 width=36) (actual time=0.004..0.005 rows=1 loops=20) |
Output: sh.shift_id, sh.location_id, sh.occurence_id, sh.datran |
Index Cond: (sh.datran && st.datran) |
Buffers: shared hit=40 |
-> Sort (cost=230.69..237.06 rows=2550 width=4) (actual time=0.024..0.025 rows=1 loops=1) |
Output: s.sensor_id, s.location_id |
Sort Key: s.sensor_id, s.location_id |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=2 |
-> Index Scan using sensor_l_ix on public.sensor s (cost=0.15..86.41 rows=2550 width=4) (actual time=0.010..0.011 rows=1 loops=1) |
Output: s.sensor_id, s.location_id |
Buffers: shared hit=2 |
-> Index Scan using tel_pk on public.telemetry t_1 (cost=0.29..1141.79 rows=21700 width=16) (actual time=0.016..83.102 rows=313399 loops=1) |
Output: t_1.t_ts, t_1.sensor_id, t_1.t_val |
Buffers: shared hit=2896 |
Planning Time: 0.658 ms |
Execution Time: 264.083 ms |
EXPLAIN