Postgres 9.6

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 order_processing_logs(
    order_number integer,
    created_time timestamp without time zone DEFAULT now()
); 
--without transaction = different timestamps
INSERT INTO order_processing_logs VALUES (1);
1 rows affected

INSERT INTO order_processing_logs VALUES (2);
1 rows affected

SELECT *, created_time = lag(created_time) OVER () as is_same FROM order_processing_logs


order_number
created_time
is_same


1
2018-09-27 14:46:33.962405
null

2
2018-09-27 14:46:33.968091
f


DELETE FROM order_processing_logs;
2 rows affected

-- with transaction = SAME timestamps
BEGIN;
INSERT INTO order_processing_logs VALUES (1);
1 rows affected

INSERT INTO order_processing_logs VALUES (2);
COMMIT;
1 rows affected

SELECT *, created_time = lag(created_time) OVER () as is_same FROM order_processing_logs


order_number
created_time
is_same


1
2018-09-27 14:46:33.983261
null

2
2018-09-27 14:46:33.983261
t

order_number	created_time	is_same
1	2018-09-27 14:46:33.962405	null
2	2018-09-27 14:46:33.968091	f

order_number	created_time	is_same
1	2018-09-27 14:46:33.983261	null
2	2018-09-27 14:46:33.983261	t