Postgres 12

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?.

select version();


version


PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE trajectories
(
    user_id integer,
    session_id bigint NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    alt double precision,
    CONSTRAINT trajectories_pkey PRIMARY KEY (session_id, "timestamp")
);
INSERT INTO trajectories (user_id, session_id, timestamp, lat, lon, alt)
VALUES(11,20070920074804,'2007-09-20 07:48:04+01',28.19737,113.006795,71),
(11,20070920074804,'2007-09-20 08:07:09+01',28.197685,113.006792,87),
(11,20070920074804,'2007-09-20 08:07:10+01',28.197685,113.00679,87),
(11,20070920074804,'2007-09-20 14:03:50+01',28.197342,113.007422,62),
(11,20070920074804,'2007-09-20 14:04:59+01',28.197108,113.00734,62),
(11,20070920074804,'2007-09-20 14:05:01+01',28.197088,113.00727,62)
6 rows affected

SELECT * FROM trajectories


user_id
session_id
timestamp
lat
lon
alt


11
20070920074804
2007-09-20 07:48:04+01
28.19737
113.006795
71

11
20070920074804
2007-09-20 08:07:09+01
28.197685
113.006792
87

11
20070920074804
2007-09-20 08:07:10+01
28.197685
113.00679
87

11
20070920074804
2007-09-20 14:03:50+01
28.197342
113.007422
62

11
20070920074804
2007-09-20 14:04:59+01
28.197108
113.00734
62

11
20070920074804
2007-09-20 14:05:01+01
28.197088
113.00727
62


select (case when grp >= 1 then session_id * 100 + grp
             else session_id
        end) as new_session_id,
       t.*
from (select t.*,
             count(*) filter (where prev_ts < timestamp - interval '30 minute') over (partition by session_id order by timestamp) as grp
      from (select t.*, 
                   lag(timestamp) over (partition by session_id order by timestamp) as prev_ts
            from trajectories t
           ) t
     ) t;


new_session_id
user_id
session_id
timestamp
lat
lon
alt
prev_ts
grp


20070920074804
11
20070920074804
2007-09-20 07:48:04+01
28.19737
113.006795
71
null
0

20070920074804
11
20070920074804
2007-09-20 08:07:09+01
28.197685
113.006792
87
2007-09-20 07:48:04+01
0

20070920074804
11
20070920074804
2007-09-20 08:07:10+01
28.197685
113.00679
87
2007-09-20 08:07:09+01
0

2007092007480401
11
20070920074804
2007-09-20 14:03:50+01
28.197342
113.007422
62
2007-09-20 08:07:10+01
1

2007092007480401
11
20070920074804
2007-09-20 14:04:59+01
28.197108
113.00734
62
2007-09-20 14:03:50+01
1

2007092007480401
11
20070920074804
2007-09-20 14:05:01+01
28.197088
113.00727
62
2007-09-20 14:04:59+01
1

user_id	session_id	timestamp	lat	lon	alt
11	20070920074804	2007-09-20 07:48:04+01	28.19737	113.006795	71
11	20070920074804	2007-09-20 08:07:09+01	28.197685	113.006792	87
11	20070920074804	2007-09-20 08:07:10+01	28.197685	113.00679	87
11	20070920074804	2007-09-20 14:03:50+01	28.197342	113.007422	62
11	20070920074804	2007-09-20 14:04:59+01	28.197108	113.00734	62
11	20070920074804	2007-09-20 14:05:01+01	28.197088	113.00727	62