Postgres 14

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 ticker (
  ticker_id smallint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, ticker text NOT NULL UNIQUE   
);

-- sample data
INSERT INTO ticker (ticker)
SELECT 'ticker_' || t FROM generate_series (1, 40) t;


CREATE TABLE tbl (
  the_date        date     NOT NULL  -- columns in this order!
, timeslot        smallint NOT NULL
, ticker_id       smallint NOT NULL REFERENCES ticker
, price_interval0 int      NOT NULL
, price_interval1 int      NOT NULL
-- ...
-- , price_interval7 int      NOT NULL
);

-- sample data for 40 tickers and the first 10 timeslots
INSERT INTO tbl
SELECT date '2022-04-20', ts, ti, (random() * 100000)::int, (random() * 100000)::int
FROM   generate_series (0,10) ts
     , generate_series (1,40) ti;

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY (ticker_id, the_date, timeslot);  -- columns in this order!
40 rows affected

440 rows affected

-- optional view to get pretty data
CREATE VIEW tbl_pretty AS
SELECT ti.ticker, the_date + interval '10 sec' * timeslot AS ts, price_interval0, price_interval1
--   , price_interval2, ...
FROM   tbl
JOIN   ticker ti USING (ticker_id);

SELECT * FROM tbl_pretty
WHERE  ticker > 'ticker_37'
LIMIT  10;


ticker
ts
price_interval0
price_interval1


ticker_4
2022-04-20 00:00:00
33950
18084

ticker_4
2022-04-20 00:00:10
38083
92679

ticker_4
2022-04-20 00:00:20
52250
99197

ticker_4
2022-04-20 00:00:30
61896
51184

ticker_4
2022-04-20 00:00:40
10916
48816

ticker_4
2022-04-20 00:00:50
19521
99963

ticker_4
2022-04-20 00:01:00
95477
77455

ticker_4
2022-04-20 00:01:10
81277
53579

ticker_4
2022-04-20 00:01:20
4843
77822

ticker_4
2022-04-20 00:01:30
26325
88867


SELECT * -- price_interval0, price_interval1  -- or just the intervals you need
FROM   tbl
WHERE  ticker_id = (SELECT ticker_id FROM ticker WHERE ticker = 'ticker_3')
AND    (the_date, timeslot) >= (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:00:00'))::int / 10)
AND    (the_date, timeslot) <  (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:01:00'))::int / 10)
ORDER  BY the_date, timeslot;


the_date
timeslot
ticker_id
price_interval0
price_interval1


2022-04-20
0
3
14502
58847

2022-04-20
1
3
50699
96424

2022-04-20
2
3
55588
63704

2022-04-20
3
3
85787
7870

2022-04-20
4
3
92590
22041

2022-04-20
5
3
37538
6216


EXPLAIN
SELECT *
FROM   tbl
WHERE  ticker_id = (SELECT ticker_id FROM ticker WHERE ticker = 'ticker_3')  --  or just: 3
AND    (the_date, timeslot) >= (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:00:00'))::int / 10)
AND    (the_date, timeslot) <  (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:01:00'))::int / 10)
ORDER  BY the_date, timeslot;


QUERY PLAN


Index Scan using tbl_pkey on tbl  (cost=8.44..16.46 rows=1 width=16)

  Index Cond: ((ticker_id = $0) AND (ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)))

  InitPlan 1 (returns $0)

    ->  Index Scan using ticker_ticker_key on ticker  (cost=0.15..8.17 rows=1 width=2)

          Index Cond: (ticker = 'ticker_3'::text)


-- same with minimal syntax
SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;


the_date
timeslot
ticker_id
price_interval0
price_interval1


2022-04-20
0
3
14502
58847

2022-04-20
1
3
50699
96424

2022-04-20
2
3
55588
63704

2022-04-20
3
3
85787
7870

2022-04-20
4
3
92590
22041

2022-04-20
5
3
37538
6216


EXPLAIN
SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;


QUERY PLAN


Index Scan using tbl_pkey on tbl  (cost=0.27..8.29 rows=1 width=16)

  Index Cond: ((ticker_id = 3) AND (ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)))


-- to demonstrate how different order of columns in PK would be less ideal:
ALTER TABLE tbl
  DROP CONSTRAINT tbl_pkey
, ADD CONSTRAINT tbl_pkey PRIMARY KEY (ticker_id, timeslot, the_date);  -- !!
EXPLAIN
SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;


QUERY PLAN


Sort  (cost=8.32..8.32 rows=1 width=16)

  Sort Key: the_date, timeslot

  ->  Index Scan using tbl_pkey on tbl  (cost=0.27..8.31 rows=1 width=16)

        Index Cond: ((ticker_id = 3) AND (ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)))


-- to demonstrate how different order of columns in PK would be less ideal:
ALTER TABLE tbl
  DROP CONSTRAINT tbl_pkey
, ADD CONSTRAINT tbl_pkey PRIMARY KEY (the_date, timeslot, ticker_id);  -- !!
EXPLAIN
SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;


QUERY PLAN


Index Scan using tbl_pkey on tbl  (cost=0.27..8.90 rows=1 width=16)

  Index Cond: ((ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)) AND (ticker_id = 3))

ticker	ts	price_interval0	price_interval1
ticker_4	2022-04-20 00:00:00	33950	18084
ticker_4	2022-04-20 00:00:10	38083	92679
ticker_4	2022-04-20 00:00:20	52250	99197
ticker_4	2022-04-20 00:00:30	61896	51184
ticker_4	2022-04-20 00:00:40	10916	48816
ticker_4	2022-04-20 00:00:50	19521	99963
ticker_4	2022-04-20 00:01:00	95477	77455
ticker_4	2022-04-20 00:01:10	81277	53579
ticker_4	2022-04-20 00:01:20	4843	77822
ticker_4	2022-04-20 00:01:30	26325	88867

the_date	timeslot	ticker_id	price_interval0	price_interval1
2022-04-20	0	3	14502	58847
2022-04-20	1	3	50699	96424
2022-04-20	2	3	55588	63704
2022-04-20	3	3	85787	7870
2022-04-20	4	3	92590	22041
2022-04-20	5	3	37538	6216