Postgres 16

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 transactions (
  id int
, hash text
);
 
INSERT INTO transactions VALUES 
  (1, 'h1')
, (2, 'h2')
, (3, 'h3')
, (4, 'h4')
, (5, 'h5')
;

CREATE TABLE logs (
  transaction_id int
, address text
, value text
);
 
INSERT INTO logs VALUES
  (1, 'a1', 'h1.a1.1')
, (1, 'a1', 'h1.a1.2')
, (1, 'a3', 'h1.a3.1')
, (2, 'a1', 'h2.a1.1')
, (2, 'a2', 'h2.a2.1')
, (2, 'a2', 'h2.a2.2')
, (2, 'a3', 'h2.a3.1')
, (3, 'a2', 'h3.a2.1')
, (4, 'a1', 'h4.a1.1')
, (5, 'a2', 'h5.a2.1')
, (5, 'a3', 'h5.a3.1')
RETURNING *;

CREATE INDEX logs_address_transaction_id ON logs (address, transaction_id) INCLUDE (value);

CREATE TABLE



INSERT 0 5



CREATE TABLE




transaction_id
address
value


1
a1
h1.a1.1

1
a1
h1.a1.2

1
a3
h1.a3.1

2
a1
h2.a1.1

2
a2
h2.a2.1

2
a2
h2.a2.2

2
a3
h2.a3.1

3
a2
h3.a2.1

4
a1
h4.a1.1

5
a2
h5.a2.1

5
a3
h5.a3.1



INSERT 0 11



CREATE INDEX


-- simple query for *few* logs per address
SELECT a.transaction_id AS id
     , ARRAY(SELECT json_build_object('address',address,'value',value) FROM logs l WHERE l.transaction_id = a.transaction_id) AS logs_array  -- array of json?
FROM  (
   SELECT DISTINCT l.transaction_id
   FROM   logs l
   WHERE  l.address = 'a2'  -- your address here (or parameterize)
   LIMIT  2  -- your LIMIT here
   ) a;


id
logs_array


2
{"{"address" : "a1", "value" : "h2.a1.1"}","{"address" : "a2", "value" : "h2.a2.1"}","{"address" : "a2", "value" : "h2.a2.2"}","{"address" : "a3", "value" : "h2.a3.1"}"}

3
{"{"address" : "a2", "value" : "h3.a2.1"}"}



SELECT 2


-- emulate skip-scan for *many* logs per address
WITH RECURSIVE adr_trans AS (
   (
   SELECT l.transaction_id
   FROM   logs l
   WHERE  l.address = 'a2'  -- your address here (or parameterize)
   ORDER  BY l.transaction_id
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l.transaction_id
           FROM   logs l
           WHERE  l.address = 'a2'  -- your address here (or parameterize)
           AND    l.transaction_id > a.transaction_id
           ORDER  BY l.transaction_id
           LIMIT  1
           )
   FROM   adr_trans a
   WHERE  a.transaction_id IS NOT NULL
   )
SELECT a.transaction_id AS id
     , (SELECT json_agg(l.*) FROM (SELECT address, value FROM logs l WHERE l.transaction_id = a.transaction_id) l) AS logs_json_array  -- json array or objects - better?
FROM   adr_trans a
WHERE  a.transaction_id IS NOT NULL  -- eliminate possible dangling null row
LIMIT  2;  -- your LIMIT here


id
logs_json_array


2
[{"address":"a1","value":"h2.a1.1"}, 
 {"address":"a2","value":"h2.a2.1"}, 
 {"address":"a2","value":"h2.a2.2"}, 
 {"address":"a3","value":"h2.a3.1"}]

3
[{"address":"a2","value":"h3.a2.1"}]



SELECT 2


-- original (bad)
WITH b AS MATERIALIZED (
   SELECT lg.transaction_id
   FROM   logs lg
   WHERE  lg.address = 'a2'
   )
SELECT id
     , (SELECT array_agg(JSON_BUILD_OBJECT('address',address,'value',value)) FROM logs WHERE transaction_id = t.id) logs_array
FROM   transactions t 
WHERE  t.id IN (SELECT transaction_id FROM b)
LIMIT  2;


id
logs_array


2
{"{"address" : "a1", "value" : "h2.a1.1"}","{"address" : "a2", "value" : "h2.a2.1"}","{"address" : "a2", "value" : "h2.a2.2"}","{"address" : "a3", "value" : "h2.a3.1"}"}

3
{"{"address" : "a2", "value" : "h3.a2.1"}"}



SELECT 2


SET enable_seqscan = off;

SET


-- demonstrate query plan
PREPARE q1 (text, int) AS
WITH RECURSIVE adr_trans AS (
   (
   SELECT l.transaction_id
   FROM   logs l
   WHERE  l.address = $1
   ORDER  BY l.transaction_id
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l.transaction_id
           FROM   logs l
           WHERE  l.address = $1
           AND    l.transaction_id > a.transaction_id
           ORDER  BY l.transaction_id
           LIMIT  1
           )
   FROM   adr_trans a
   WHERE  a.transaction_id IS NOT NULL
   )
SELECT a.transaction_id AS id
     , (SELECT json_agg(l.*) FROM (SELECT address, value FROM logs l WHERE l.transaction_id = a.transaction_id) l) AS logs
FROM   adr_trans a
WHERE  a.transaction_id IS NOT NULL
LIMIT  $2;

PREPARE


EXECUTE q1 ('a2', 2);


id
logs


2
[{"address":"a1","value":"h2.a1.1"}, 
 {"address":"a2","value":"h2.a2.1"}, 
 {"address":"a2","value":"h2.a2.2"}, 
 {"address":"a3","value":"h2.a3.1"}]

3
[{"address":"a2","value":"h3.a2.1"}]



SELECT 2


-- to drag in index for minimal samplw (demo only)
SET enable_seqscan = off;

SET


-- all index-only scans
EXPLAIN ANALYZE EXECUTE q1 ('a2', 3);


QUERY PLAN


Limit  (cost=826.66..863.45 rows=3 width=36) (actual time=0.081..0.216 rows=3 loops=1)

  CTE adr_trans

    ->  Recursive Union  (cost=0.14..826.66 rows=101 width=4) (actual time=0.020..0.118 rows=3 loops=1)

          ->  Limit  (cost=0.14..8.15 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)

                ->  Index Only Scan using logs_address_transaction_id on logs l_1  (cost=0.14..8.15 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

                      Index Cond: (address = 'a2'::text)

                      Heap Fetches: 1

          ->  WorkTable Scan on adr_trans a_1  (cost=0.00..81.75 rows=10 width=4) (actual time=0.045..0.046 rows=1 loops=2)

                Filter: (transaction_id IS NOT NULL)

                SubPlan 1

                  ->  Limit  (cost=0.14..8.15 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=2)

                        ->  Index Only Scan using logs_address_transaction_id on logs l  (cost=0.14..8.15 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=2)

                              Index Cond: ((address = 'a2'::text) AND (transaction_id > a_1.transaction_id))

                              Heap Fetches: 2

  ->  CTE Scan on adr_trans a  (cost=0.00..1226.27 rows=100 width=36) (actual time=0.079..0.211 rows=3 loops=1)

        Filter: (transaction_id IS NOT NULL)

        SubPlan 3

          ->  Aggregate  (cost=12.23..12.24 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=3)

                ->  Index Only Scan using logs_address_transaction_id on logs l_2  (cost=0.14..12.23 rows=1 width=64) (actual time=0.011..0.013 rows=2 loops=3)

                      Index Cond: (transaction_id = a.transaction_id)

                      Heap Fetches: 7

Planning Time: 0.430 ms

Execution Time: 0.312 ms



EXPLAIN

transaction_id	address	value
1	a1	h1.a1.1
1	a1	h1.a1.2
1	a3	h1.a3.1
2	a1	h2.a1.1
2	a2	h2.a2.1
2	a2	h2.a2.2
2	a3	h2.a3.1
3	a2	h3.a2.1
4	a1	h4.a1.1
5	a2	h5.a2.1
5	a3	h5.a3.1

id	logs_array
2	{"{"address" : "a1", "value" : "h2.a1.1"}","{"address" : "a2", "value" : "h2.a2.1"}","{"address" : "a2", "value" : "h2.a2.2"}","{"address" : "a3", "value" : "h2.a3.1"}"}
3	{"{"address" : "a2", "value" : "h3.a2.1"}"}

id	logs_json_array
2	[{"address":"a1","value":"h2.a1.1"}, {"address":"a2","value":"h2.a2.1"}, {"address":"a2","value":"h2.a2.2"}, {"address":"a3","value":"h2.a3.1"}]
3	[{"address":"a2","value":"h3.a2.1"}]