Postgres 11

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 tick (
    symbol character(9) NOT NULL,
    price real NOT NULL,
    "time" timestamp with time zone NOT NULL
);

CREATE TABLE


INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.9, '2019-04-11 10:32:00.361294-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.6, '2019-04-11 10:32:00.429346-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.7, '2019-04-11 10:32:01.03363-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:01.085657-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.6, '2019-04-11 10:32:01.141192-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:01.245584-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:01.353293-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:01.611854-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:02.784545-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:02.888302-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:02.947919-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:03.055718-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:03.158856-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174, '2019-04-11 10:32:03.262169-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:03.806712-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.3873-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:04.444778-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.504045-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:04.824775-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.880204-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:04.98412-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:05.546972-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:06.126979-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:06.180949-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:06.233617-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.9, '2019-04-11 10:32:06.53779-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:06.83216-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.099359-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:07.159276-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.220017-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:07.436384-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.492972-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:07.599031-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:08.528171-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26172.9, '2019-04-11 10:32:08.803254-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:08.954501-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26172.9, '2019-04-11 10:32:09.056822-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:09.160093-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:09.813164-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:10.168529-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:10.223418-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:10.32763-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:10.383433-05');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT price, "time", 
       LAST_VALUE(price) OVER (ORDER BY "time" ASC 
                               RANGE BETWEEN CURRENT ROW 
                                     AND '5 second' FOLLOWING) last_price,
       LAST_VALUE("time") OVER (ORDER BY "time" ASC 
                                RANGE BETWEEN CURRENT ROW 
                                      AND '5 second' FOLLOWING) last_time
FROM tick
ORDER BY "time"


price
time
last_price
last_time


26175.9
2019-04-11 16:32:00.361294+01
26173.1
2019-04-11 16:32:04.98412+01

26175.6
2019-04-11 16:32:00.429346+01
26173.1
2019-04-11 16:32:04.98412+01

26173.7
2019-04-11 16:32:01.03363+01
26174.1
2019-04-11 16:32:05.546972+01

26174.6
2019-04-11 16:32:01.085657+01
26174.1
2019-04-11 16:32:05.546972+01

26175.6
2019-04-11 16:32:01.141192+01
26173.8
2019-04-11 16:32:06.126979+01

26173.8
2019-04-11 16:32:01.245584+01
26173.8
2019-04-11 16:32:06.233617+01

26174.6
2019-04-11 16:32:01.353293+01
26173.8
2019-04-11 16:32:06.233617+01

26173.8
2019-04-11 16:32:01.611854+01
26173.9
2019-04-11 16:32:06.53779+01

26174.9
2019-04-11 16:32:02.784545+01
26174.6
2019-04-11 16:32:07.599031+01

26173.8
2019-04-11 16:32:02.888302+01
26174.6
2019-04-11 16:32:07.599031+01

26174.9
2019-04-11 16:32:02.947919+01
26174.6
2019-04-11 16:32:07.599031+01

26173.8
2019-04-11 16:32:03.055718+01
26174.6
2019-04-11 16:32:07.599031+01

26174.9
2019-04-11 16:32:03.158856+01
26174.6
2019-04-11 16:32:07.599031+01

26174
2019-04-11 16:32:03.262169+01
26174.6
2019-04-11 16:32:07.599031+01

26173.8
2019-04-11 16:32:03.806712+01
26172.9
2019-04-11 16:32:08.803254+01

26173.6
2019-04-11 16:32:04.3873+01
26174.6
2019-04-11 16:32:09.160093+01

26173.8
2019-04-11 16:32:04.444778+01
26174.6
2019-04-11 16:32:09.160093+01

26173.6
2019-04-11 16:32:04.504045+01
26174.6
2019-04-11 16:32:09.160093+01

26173.1
2019-04-11 16:32:04.824775+01
26173.4
2019-04-11 16:32:09.813164+01

26173.6
2019-04-11 16:32:04.880204+01
26173.4
2019-04-11 16:32:09.813164+01

26173.1
2019-04-11 16:32:04.98412+01
26173.4
2019-04-11 16:32:09.813164+01

26174.1
2019-04-11 16:32:05.546972+01
26173.4
2019-04-11 16:32:10.383433+01

26173.8
2019-04-11 16:32:06.126979+01
26173.4
2019-04-11 16:32:10.383433+01

26174.1
2019-04-11 16:32:06.180949+01
26173.4
2019-04-11 16:32:10.383433+01

26173.8
2019-04-11 16:32:06.233617+01
26173.4
2019-04-11 16:32:10.383433+01

26173.9
2019-04-11 16:32:06.53779+01
26173.4
2019-04-11 16:32:10.383433+01

26173.1
2019-04-11 16:32:06.83216+01
26173.4
2019-04-11 16:32:10.383433+01

26174.1
2019-04-11 16:32:07.099359+01
26173.4
2019-04-11 16:32:10.383433+01

26173.1
2019-04-11 16:32:07.159276+01
26173.4
2019-04-11 16:32:10.383433+01

26174.1
2019-04-11 16:32:07.220017+01
26173.4
2019-04-11 16:32:10.383433+01

26174.6
2019-04-11 16:32:07.436384+01
26173.4
2019-04-11 16:32:10.383433+01

26174.1
2019-04-11 16:32:07.492972+01
26173.4
2019-04-11 16:32:10.383433+01

26174.6
2019-04-11 16:32:07.599031+01
26173.4
2019-04-11 16:32:10.383433+01

26173.1
2019-04-11 16:32:08.528171+01
26173.4
2019-04-11 16:32:10.383433+01

26172.9
2019-04-11 16:32:08.803254+01
26173.4
2019-04-11 16:32:10.383433+01

26173.1
2019-04-11 16:32:08.954501+01
26173.4
2019-04-11 16:32:10.383433+01

26172.9
2019-04-11 16:32:09.056822+01
26173.4
2019-04-11 16:32:10.383433+01

26174.6
2019-04-11 16:32:09.160093+01
26173.4
2019-04-11 16:32:10.383433+01

26173.4
2019-04-11 16:32:09.813164+01
26173.4
2019-04-11 16:32:10.383433+01

26174.6
2019-04-11 16:32:10.168529+01
26173.4
2019-04-11 16:32:10.383433+01

26173.4
2019-04-11 16:32:10.223418+01
26173.4
2019-04-11 16:32:10.383433+01

26174.6
2019-04-11 16:32:10.32763+01
26173.4
2019-04-11 16:32:10.383433+01

26173.4
2019-04-11 16:32:10.383433+01
26173.4
2019-04-11 16:32:10.383433+01



SELECT 43

price	time	last_price	last_time
26175.9	2019-04-11 16:32:00.361294+01	26173.1	2019-04-11 16:32:04.98412+01
26175.6	2019-04-11 16:32:00.429346+01	26173.1	2019-04-11 16:32:04.98412+01
26173.7	2019-04-11 16:32:01.03363+01	26174.1	2019-04-11 16:32:05.546972+01
26174.6	2019-04-11 16:32:01.085657+01	26174.1	2019-04-11 16:32:05.546972+01
26175.6	2019-04-11 16:32:01.141192+01	26173.8	2019-04-11 16:32:06.126979+01
26173.8	2019-04-11 16:32:01.245584+01	26173.8	2019-04-11 16:32:06.233617+01
26174.6	2019-04-11 16:32:01.353293+01	26173.8	2019-04-11 16:32:06.233617+01
26173.8	2019-04-11 16:32:01.611854+01	26173.9	2019-04-11 16:32:06.53779+01
26174.9	2019-04-11 16:32:02.784545+01	26174.6	2019-04-11 16:32:07.599031+01
26173.8	2019-04-11 16:32:02.888302+01	26174.6	2019-04-11 16:32:07.599031+01
26174.9	2019-04-11 16:32:02.947919+01	26174.6	2019-04-11 16:32:07.599031+01
26173.8	2019-04-11 16:32:03.055718+01	26174.6	2019-04-11 16:32:07.599031+01
26174.9	2019-04-11 16:32:03.158856+01	26174.6	2019-04-11 16:32:07.599031+01
26174	2019-04-11 16:32:03.262169+01	26174.6	2019-04-11 16:32:07.599031+01
26173.8	2019-04-11 16:32:03.806712+01	26172.9	2019-04-11 16:32:08.803254+01
26173.6	2019-04-11 16:32:04.3873+01	26174.6	2019-04-11 16:32:09.160093+01
26173.8	2019-04-11 16:32:04.444778+01	26174.6	2019-04-11 16:32:09.160093+01
26173.6	2019-04-11 16:32:04.504045+01	26174.6	2019-04-11 16:32:09.160093+01
26173.1	2019-04-11 16:32:04.824775+01	26173.4	2019-04-11 16:32:09.813164+01
26173.6	2019-04-11 16:32:04.880204+01	26173.4	2019-04-11 16:32:09.813164+01
26173.1	2019-04-11 16:32:04.98412+01	26173.4	2019-04-11 16:32:09.813164+01
26174.1	2019-04-11 16:32:05.546972+01	26173.4	2019-04-11 16:32:10.383433+01
26173.8	2019-04-11 16:32:06.126979+01	26173.4	2019-04-11 16:32:10.383433+01
26174.1	2019-04-11 16:32:06.180949+01	26173.4	2019-04-11 16:32:10.383433+01
26173.8	2019-04-11 16:32:06.233617+01	26173.4	2019-04-11 16:32:10.383433+01
26173.9	2019-04-11 16:32:06.53779+01	26173.4	2019-04-11 16:32:10.383433+01
26173.1	2019-04-11 16:32:06.83216+01	26173.4	2019-04-11 16:32:10.383433+01
26174.1	2019-04-11 16:32:07.099359+01	26173.4	2019-04-11 16:32:10.383433+01
26173.1	2019-04-11 16:32:07.159276+01	26173.4	2019-04-11 16:32:10.383433+01
26174.1	2019-04-11 16:32:07.220017+01	26173.4	2019-04-11 16:32:10.383433+01
26174.6	2019-04-11 16:32:07.436384+01	26173.4	2019-04-11 16:32:10.383433+01
26174.1	2019-04-11 16:32:07.492972+01	26173.4	2019-04-11 16:32:10.383433+01
26174.6	2019-04-11 16:32:07.599031+01	26173.4	2019-04-11 16:32:10.383433+01
26173.1	2019-04-11 16:32:08.528171+01	26173.4	2019-04-11 16:32:10.383433+01
26172.9	2019-04-11 16:32:08.803254+01	26173.4	2019-04-11 16:32:10.383433+01
26173.1	2019-04-11 16:32:08.954501+01	26173.4	2019-04-11 16:32:10.383433+01
26172.9	2019-04-11 16:32:09.056822+01	26173.4	2019-04-11 16:32:10.383433+01
26174.6	2019-04-11 16:32:09.160093+01	26173.4	2019-04-11 16:32:10.383433+01
26173.4	2019-04-11 16:32:09.813164+01	26173.4	2019-04-11 16:32:10.383433+01
26174.6	2019-04-11 16:32:10.168529+01	26173.4	2019-04-11 16:32:10.383433+01
26173.4	2019-04-11 16:32:10.223418+01	26173.4	2019-04-11 16:32:10.383433+01
26174.6	2019-04-11 16:32:10.32763+01	26173.4	2019-04-11 16:32:10.383433+01
26173.4	2019-04-11 16:32:10.383433+01	26173.4	2019-04-11 16:32:10.383433+01