Postgres 13

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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE ts_portfolio  -- time series
(
  txn_id  INTEGER NOT NULL,
  company TEXT NOT NULL,
  price   INTEGER NOT NULL,
  txn_ts  TIMESTAMP NOT NULL
);
CREATE INDEX tx_ix ON ts_portfolio (txn_id);
CREATE INDEX ts_ix ON ts_portfolio (txn_ts);
INSERT INTO ts_portfolio VALUES
(1234,       'DB',     10,  '07/11/21 00:12:00'),    -- record inserted on 07/11/21
(2345,       'DB',     20,  '08/11/21 00:12:00'),    -- record inserted on 08/11/21
(3456,       'DB',     30,  '09/11/21 00:12:00'),    -- record inserted on 09/11/21
(4567,       'DB',     40,  '10/11/21 00:12:00'),    -- record inserted on 10/11/21
(5678,       'DB',     50,  '11/11/21 00:12:00');    -- record inserted on 11/11/21
5 rows affected

SELECT 
  txn_id, company, price, txn_ts
FROM ts_portfolio;


txn_id
company
price
txn_ts


1234
DB
10
2021-11-07 00:12:00

2345
DB
20
2021-11-08 00:12:00

3456
DB
30
2021-11-09 00:12:00

4567
DB
40
2021-11-10 00:12:00

5678
DB
50
2021-11-11 00:12:00


--
-- AS OF query
-- 

SELECT company, price 
FROM ts_portfolio
WHERE company = 'DB'
AND txn_ts <= '09/11/21 00:00:00' 
ORDER BY txn_ts DESC
LIMIT 1;


company
price


DB
20


--
-- UNION better here? See EXPLAIN below - not important for this debate!
--

WITH cte1 AS 
(
  SELECT txn_id AS t_start
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 00:00:00'
  ORDER BY txn_ts DESC
  LIMIT 1
), cte2 AS
(
  SELECT txn_id AS t_end
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 23:59:59.999999'
  ORDER BY txn_ts DESC
  LIMIT 1
)
SELECT 
  t.txn_id, t.company, t.price
FROM 
  ts_portfolio t
WHERE txn_id BETWEEN (SELECT t_start FROM cte1) AND (SELECT t_end FROM cte2);


txn_id
company
price


2345
DB
20

3456
DB
30


CREATE TABLE tdb_portfolio  -- temporal (versioning) db
(
  txn_id     INTEGER NOT NULL,
  company    TEXT NOT NULL,
  price      INTEGER NOT NULL,
  txn_t_from TIMESTAMP NOT NULL,
  txn_t_to   TIMESTAMP NOT NULL  -- extra field
);
INSERT INTO tdb_portfolio VALUES
(1000, 'DB',  5, '01/01/1900',        '07/11/21 00:12:00'),  -- inserted in the past, updated 07/11 @ 12:00
(1234, 'DB', 10, '07/11/21 00:12:00', '08/11/21 00:12:00'),  -- record inserted on 07/11/21 - updated 08/11 @ 12:00
(2345, 'DB', 20, '08/11/21 00:12:00', '09/11/21 00:12:00'),  -- record inserted on 08/11/21 - and so on...
(3456, 'DB', 30, '09/11/21 00:12:00', '10/11/21 00:12:00'),  -- record inserted on 09/11/21
(4567, 'DB', 40, '10/11/21 00:12:00', '11/11/21 00:12:00'),  -- record inserted on 10/11/21
(5678, 'DB', 50, '11/11/21 00:12:00', '19/01/2038');  -- record inserted on 11/11/21 - never updated (effectively INFINITY until next update)
6 rows affected

--
-- AS OF query  - point in time
--

SELECT txn_id, company, price
FROM tdb_portfolio AS OF '09/11/2021 00:00:00';
ERROR:  syntax error at or near "'09/11/2021 00:00:00'"
LINE 6: FROM tdb_portfolio AS OF '09/11/2021 00:00:00';
                                 ^


--
-- BETWEEN query
--

SELECT txn_id, company, price FROM tdb_portfolio
FOR SYSTEM_TIME BETWEEN ('09/11/2021 00:00:00' AND '09/11/2021 23:59:59.999999');
  
ERROR:  syntax error at or near "SYSTEM_TIME"
LINE 6: FOR SYSTEM_TIME BETWEEN ('09/11/2021 00:00:00' AND '09/11/20...
            ^


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH cte1 AS 
(
  SELECT txn_id AS t_start
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 00:00:00'
  ORDER BY txn_ts DESC
  LIMIT 1
), cte2 AS
(
  SELECT txn_id AS t_end
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 23:59:59.999999'
  ORDER BY txn_ts DESC
  LIMIT 1
)
SELECT 
  t.txn_id, t.company, t.price
FROM 
  ts_portfolio t
WHERE txn_id BETWEEN (SELECT t_start FROM cte1) AND (SELECT t_end FROM cte2);


QUERY PLAN


Bitmap Heap Scan on public.ts_portfolio t  (cost=48.62..57.11 rows=5 width=40) (actual time=0.043..0.045 rows=2 loops=1)

  Output: t.txn_id, t.company, t.price

  Recheck Cond: ((t.txn_id >= $0) AND (t.txn_id <= $1))

  Heap Blocks: exact=1

  Buffers: shared hit=6

  InitPlan 1 (returns $0)

    ->  Subquery Scan on cte1  (cost=22.20..22.21 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)

          Output: cte1.t_start

          Buffers: shared hit=2

          ->  Limit  (cost=22.20..22.20 rows=1 width=12) (actual time=0.023..0.023 rows=1 loops=1)

                Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                Buffers: shared hit=2

                ->  Sort  (cost=22.20..22.20 rows=2 width=12) (actual time=0.022..0.022 rows=1 loops=1)

                      Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                      Sort Key: ts_portfolio.txn_ts DESC

                      Sort Method: quicksort  Memory: 25kB

                      Buffers: shared hit=2

                      ->  Bitmap Heap Scan on public.ts_portfolio  (cost=6.83..22.19 rows=2 width=12) (actual time=0.009..0.009 rows=2 loops=1)

                            Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                            Recheck Cond: (ts_portfolio.txn_ts <= '2021-11-09 00:00:00'::timestamp without time zone)

                            Filter: (ts_portfolio.company = 'DB'::text)

                            Heap Blocks: exact=1

                            Buffers: shared hit=2

                            ->  Bitmap Index Scan on ts_ix  (cost=0.00..6.83 rows=357 width=0) (actual time=0.004..0.005 rows=2 loops=1)

                                  Index Cond: (ts_portfolio.txn_ts <= '2021-11-09 00:00:00'::timestamp without time zone)

                                  Buffers: shared hit=1

  InitPlan 2 (returns $1)

    ->  Subquery Scan on cte2  (cost=22.20..22.21 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

          Output: cte2.t_end

          Buffers: shared hit=2

          ->  Limit  (cost=22.20..22.20 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=1)

                Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                Buffers: shared hit=2

                ->  Sort  (cost=22.20..22.20 rows=2 width=12) (actual time=0.012..0.013 rows=1 loops=1)

                      Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                      Sort Key: ts_portfolio_1.txn_ts DESC

                      Sort Method: top-N heapsort  Memory: 25kB

                      Buffers: shared hit=2

                      ->  Bitmap Heap Scan on public.ts_portfolio ts_portfolio_1  (cost=6.83..22.19 rows=2 width=12) (actual time=0.005..0.006 rows=3 loops=1)

                            Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                            Recheck Cond: (ts_portfolio_1.txn_ts <= '2021-11-09 23:59:59.999999'::timestamp without time zone)

                            Filter: (ts_portfolio_1.company = 'DB'::text)

                            Heap Blocks: exact=1

                            Buffers: shared hit=2

                            ->  Bitmap Index Scan on ts_ix  (cost=0.00..6.83 rows=357 width=0) (actual time=0.002..0.002 rows=3 loops=1)

                                  Index Cond: (ts_portfolio_1.txn_ts <= '2021-11-09 23:59:59.999999'::timestamp without time zone)

                                  Buffers: shared hit=1

  ->  Bitmap Index Scan on tx_ix  (cost=0.00..4.20 rows=5 width=0) (actual time=0.041..0.041 rows=2 loops=1)

        Index Cond: ((t.txn_id >= $0) AND (t.txn_id <= $1))

        Buffers: shared hit=5

Planning Time: 0.225 ms

Execution Time: 0.110 ms


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM
(
  SELECT txn_id AS t_start
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 00:00:00'
  ORDER BY txn_ts DESC
  LIMIT 1
) AS t1
UNION
(
  SELECT txn_id AS t_end
  FROM ts_portfolio
  WHERE company = 'DB'
  AND txn_ts <= '09/11/21 23:59:59.999999'
  ORDER BY txn_ts DESC
  LIMIT 1
) 


QUERY PLAN


Unique  (cost=44.45..44.46 rows=2 width=4) (actual time=0.056..0.059 rows=2 loops=1)

  Output: t1.t_start

  Buffers: shared hit=4

  ->  Sort  (cost=44.45..44.45 rows=2 width=4) (actual time=0.056..0.057 rows=2 loops=1)

        Output: t1.t_start

        Sort Key: t1.t_start

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=4

        ->  Append  (cost=22.20..44.44 rows=2 width=4) (actual time=0.011..0.050 rows=2 loops=1)

              Buffers: shared hit=4

              ->  Subquery Scan on t1  (cost=22.20..22.21 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

                    Output: t1.t_start

                    Buffers: shared hit=2

                    ->  Limit  (cost=22.20..22.20 rows=1 width=12) (actual time=0.010..0.011 rows=1 loops=1)

                          Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                          Buffers: shared hit=2

                          ->  Sort  (cost=22.20..22.20 rows=2 width=12) (actual time=0.009..0.010 rows=1 loops=1)

                                Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                                Sort Key: ts_portfolio.txn_ts DESC

                                Sort Method: quicksort  Memory: 25kB

                                Buffers: shared hit=2

                                ->  Bitmap Heap Scan on public.ts_portfolio  (cost=6.83..22.19 rows=2 width=12) (actual time=0.006..0.007 rows=2 loops=1)

                                      Output: ts_portfolio.txn_id, ts_portfolio.txn_ts

                                      Recheck Cond: (ts_portfolio.txn_ts <= '2021-11-09 00:00:00'::timestamp without time zone)

                                      Filter: (ts_portfolio.company = 'DB'::text)

                                      Heap Blocks: exact=1

                                      Buffers: shared hit=2

                                      ->  Bitmap Index Scan on ts_ix  (cost=0.00..6.83 rows=357 width=0) (actual time=0.003..0.003 rows=2 loops=1)

                                            Index Cond: (ts_portfolio.txn_ts <= '2021-11-09 00:00:00'::timestamp without time zone)

                                            Buffers: shared hit=1

              ->  Subquery Scan on "*SELECT* 2"  (cost=22.20..22.21 rows=1 width=4) (actual time=0.037..0.037 rows=1 loops=1)

                    Output: "*SELECT* 2".t_end

                    Buffers: shared hit=2

                    ->  Limit  (cost=22.20..22.20 rows=1 width=12) (actual time=0.036..0.037 rows=1 loops=1)

                          Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                          Buffers: shared hit=2

                          ->  Sort  (cost=22.20..22.20 rows=2 width=12) (actual time=0.036..0.036 rows=1 loops=1)

                                Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                                Sort Key: ts_portfolio_1.txn_ts DESC

                                Sort Method: top-N heapsort  Memory: 25kB

                                Buffers: shared hit=2

                                ->  Bitmap Heap Scan on public.ts_portfolio ts_portfolio_1  (cost=6.83..22.19 rows=2 width=12) (actual time=0.023..0.024 rows=3 loops=1)

                                      Output: ts_portfolio_1.txn_id, ts_portfolio_1.txn_ts

                                      Recheck Cond: (ts_portfolio_1.txn_ts <= '2021-11-09 23:59:59.999999'::timestamp without time zone)

                                      Filter: (ts_portfolio_1.company = 'DB'::text)

                                      Heap Blocks: exact=1

                                      Buffers: shared hit=2

                                      ->  Bitmap Index Scan on ts_ix  (cost=0.00..6.83 rows=357 width=0) (actual time=0.002..0.002 rows=3 loops=1)

                                            Index Cond: (ts_portfolio_1.txn_ts <= '2021-11-09 23:59:59.999999'::timestamp without time zone)

                                            Buffers: shared hit=1

Planning:

  Buffers: shared hit=6

Planning Time: 0.128 ms

Execution Time: 0.125 ms
txn_id	company	price	txn_ts
1234	DB	10	2021-11-07 00:12:00
2345	DB	20	2021-11-08 00:12:00
3456	DB	30	2021-11-09 00:12:00
4567	DB	40	2021-11-10 00:12:00
5678	DB	50	2021-11-11 00:12:00