add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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