Postgres 15

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

CREATE TABLE tbl (id serial, ts timestamp);
INSERT INTO tbl(ts) VALUES
   ('2014-05-27 12:03:20')
 , ('2014-10-30 00:00:00')
 , ('2013-10-19 23:59:59')
 , ('2013-07-10 23:59:59.999999');

CREATE TABLE



INSERT 0 4


-- inclusive upper bound - based on cast to date
SELECT *
FROM   tbl
CROSS  JOIN LATERAL (
   VALUES
   ('last_sec_of_day0', ts::date + 1 - interval '1 sec')
 , ('last_sec_of_day1', ts::date + interval '1 day - 1 sec')
 , ('last_sec_of_day2', ts::date + interval '23:59:59')
 , ('last_sec_of_day3', ts::date + time     '23:59:59')
 , ('last_ts_of_day1' , ts::date + interval '1 day - 1 microsecond')
 , ('last_ts_of_day2' , ts::date + interval '23:59:59.999999')
 , ('last_ts_of_day3' , ts::date + time     '23:59:59.999999')
    ) x(name, value);


id
ts
name
value


1
2014-05-27 12:03:20
last_sec_of_day0
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_sec_of_day1
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_sec_of_day2
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_sec_of_day3
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_ts_of_day1
2014-05-27 23:59:59.999999

1
2014-05-27 12:03:20
last_ts_of_day2
2014-05-27 23:59:59.999999

1
2014-05-27 12:03:20
last_ts_of_day3
2014-05-27 23:59:59.999999

2
2014-10-30 00:00:00
last_sec_of_day0
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_sec_of_day1
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_sec_of_day2
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_sec_of_day3
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_ts_of_day1
2014-10-30 23:59:59.999999

2
2014-10-30 00:00:00
last_ts_of_day2
2014-10-30 23:59:59.999999

2
2014-10-30 00:00:00
last_ts_of_day3
2014-10-30 23:59:59.999999

3
2013-10-19 23:59:59
last_sec_of_day0
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_sec_of_day1
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_sec_of_day2
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_sec_of_day3
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_ts_of_day1
2013-10-19 23:59:59.999999

3
2013-10-19 23:59:59
last_ts_of_day2
2013-10-19 23:59:59.999999

3
2013-10-19 23:59:59
last_ts_of_day3
2013-10-19 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_sec_of_day0
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_sec_of_day1
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_sec_of_day2
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_sec_of_day3
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_ts_of_day1
2013-07-10 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_ts_of_day2
2013-07-10 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_ts_of_day3
2013-07-10 23:59:59.999999



SELECT 28


-- inclusive upper bound - based on date_trunc()
SELECT *
FROM   tbl
CROSS  JOIN LATERAL (
   VALUES
-- ('last_sec_of_day0', ts::date + 1 - interval '1 sec')  -- only works with type date
   ('last_sec_of_day1', date_trunc('day', ts) + interval '1 day - 1 sec')
 , ('last_sec_of_day2', date_trunc('day', ts) + interval '23:59:59')
 , ('last_sec_of_day3', date_trunc('day', ts) + time     '23:59:59') 
 , ('last_ts_of_day1' , date_trunc('day', ts) + interval '1 day - 1 microsecond')
 , ('last_ts_of_day2' , date_trunc('day', ts) + interval '23:59:59.999999')
 , ('last_ts_of_day3' , date_trunc('day', ts) + time     '23:59:59.999999')
    ) x(name, value);


id
ts
name
value


1
2014-05-27 12:03:20
last_sec_of_day1
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_sec_of_day2
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_sec_of_day3
2014-05-27 23:59:59

1
2014-05-27 12:03:20
last_ts_of_day1
2014-05-27 23:59:59.999999

1
2014-05-27 12:03:20
last_ts_of_day2
2014-05-27 23:59:59.999999

1
2014-05-27 12:03:20
last_ts_of_day3
2014-05-27 23:59:59.999999

2
2014-10-30 00:00:00
last_sec_of_day1
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_sec_of_day2
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_sec_of_day3
2014-10-30 23:59:59

2
2014-10-30 00:00:00
last_ts_of_day1
2014-10-30 23:59:59.999999

2
2014-10-30 00:00:00
last_ts_of_day2
2014-10-30 23:59:59.999999

2
2014-10-30 00:00:00
last_ts_of_day3
2014-10-30 23:59:59.999999

3
2013-10-19 23:59:59
last_sec_of_day1
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_sec_of_day2
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_sec_of_day3
2013-10-19 23:59:59

3
2013-10-19 23:59:59
last_ts_of_day1
2013-10-19 23:59:59.999999

3
2013-10-19 23:59:59
last_ts_of_day2
2013-10-19 23:59:59.999999

3
2013-10-19 23:59:59
last_ts_of_day3
2013-10-19 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_sec_of_day1
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_sec_of_day2
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_sec_of_day3
2013-07-10 23:59:59

4
2013-07-10 23:59:59.999999
last_ts_of_day1
2013-07-10 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_ts_of_day2
2013-07-10 23:59:59.999999

4
2013-07-10 23:59:59.999999
last_ts_of_day3
2013-07-10 23:59:59.999999



SELECT 24


-- exclusive upper bound
SELECT *
     , ts::date + 1 AS next_date
     , date_trunc('day', ts) + interval '1 day' AS next_day_1st_ts
FROM   tbl;


id
ts
next_date
next_day_1st_ts


1
2014-05-27 12:03:20
2014-05-28
2014-05-28 00:00:00

2
2014-10-30 00:00:00
2014-10-31
2014-10-31 00:00:00

3
2013-10-19 23:59:59
2013-10-20
2013-10-20 00:00:00

4
2013-07-10 23:59:59.999999
2013-07-11
2013-07-11 00:00:00



SELECT 4

id	ts	name	value
1	2014-05-27 12:03:20	last_sec_of_day0	2014-05-27 23:59:59
1	2014-05-27 12:03:20	last_sec_of_day1	2014-05-27 23:59:59
1	2014-05-27 12:03:20	last_sec_of_day2	2014-05-27 23:59:59
1	2014-05-27 12:03:20	last_sec_of_day3	2014-05-27 23:59:59
1	2014-05-27 12:03:20	last_ts_of_day1	2014-05-27 23:59:59.999999
1	2014-05-27 12:03:20	last_ts_of_day2	2014-05-27 23:59:59.999999
1	2014-05-27 12:03:20	last_ts_of_day3	2014-05-27 23:59:59.999999
2	2014-10-30 00:00:00	last_sec_of_day0	2014-10-30 23:59:59
2	2014-10-30 00:00:00	last_sec_of_day1	2014-10-30 23:59:59
2	2014-10-30 00:00:00	last_sec_of_day2	2014-10-30 23:59:59
2	2014-10-30 00:00:00	last_sec_of_day3	2014-10-30 23:59:59
2	2014-10-30 00:00:00	last_ts_of_day1	2014-10-30 23:59:59.999999
2	2014-10-30 00:00:00	last_ts_of_day2	2014-10-30 23:59:59.999999
2	2014-10-30 00:00:00	last_ts_of_day3	2014-10-30 23:59:59.999999
3	2013-10-19 23:59:59	last_sec_of_day0	2013-10-19 23:59:59
3	2013-10-19 23:59:59	last_sec_of_day1	2013-10-19 23:59:59
3	2013-10-19 23:59:59	last_sec_of_day2	2013-10-19 23:59:59
3	2013-10-19 23:59:59	last_sec_of_day3	2013-10-19 23:59:59
3	2013-10-19 23:59:59	last_ts_of_day1	2013-10-19 23:59:59.999999
3	2013-10-19 23:59:59	last_ts_of_day2	2013-10-19 23:59:59.999999
3	2013-10-19 23:59:59	last_ts_of_day3	2013-10-19 23:59:59.999999
4	2013-07-10 23:59:59.999999	last_sec_of_day0	2013-07-10 23:59:59
4	2013-07-10 23:59:59.999999	last_sec_of_day1	2013-07-10 23:59:59
4	2013-07-10 23:59:59.999999	last_sec_of_day2	2013-07-10 23:59:59
4	2013-07-10 23:59:59.999999	last_sec_of_day3	2013-07-10 23:59:59
4	2013-07-10 23:59:59.999999	last_ts_of_day1	2013-07-10 23:59:59.999999
4	2013-07-10 23:59:59.999999	last_ts_of_day2	2013-07-10 23:59:59.999999
4	2013-07-10 23:59:59.999999	last_ts_of_day3	2013-07-10 23:59:59.999999

id	ts	next_date	next_day_1st_ts
1	2014-05-27 12:03:20	2014-05-28	2014-05-28 00:00:00
2	2014-10-30 00:00:00	2014-10-31	2014-10-31 00:00:00
3	2013-10-19 23:59:59	2013-10-20	2013-10-20 00:00:00
4	2013-07-10 23:59:59.999999	2013-07-11	2013-07-11 00:00:00