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 ( hash varchar, t_value varchar );
insert into transactions values
('h1','v1'), ('h2','v2'), ('h3','v3'), ('h4','v4'), ('h5','v5');
Select * From transactions;
CREATE TABLE
INSERT 0 5
| hash | t_value |
|---|---|
| h1 | v1 |
| h2 | v2 |
| h3 | v3 |
| h4 | v4 |
| h5 | v5 |
SELECT 5
create table logs ( transaction_hash varchar, address varchar, l_value varchar );
insert into logs values
('h1', 'a1', 'h1.a1.1'), ('h1', 'a1', 'h1.a1.2'), ('h1', 'a3', 'h1.a3.1'),
('h2', 'a1', 'h2.a1.1'), ('h2', 'a2', 'h2.a2.1'), ('h2', 'a2', 'h2.a2.2'),
('h2', 'a3', 'h2.a3.1'), ('h3', 'a2', 'h3.a2.1'), ('h4', 'a1', 'h4.a1.1'),
('h5', 'a2', 'h5.a2.1'), ('h5', 'a3', 'h5.a3.1');
Select * From logs;
CREATE TABLE
INSERT 0 11
| transaction_hash | address | l_value |
|---|---|---|
| h1 | a1 | h1.a1.1 |
| h1 | a1 | h1.a1.2 |
| h1 | a3 | h1.a3.1 |
| h2 | a1 | h2.a1.1 |
| h2 | a2 | h2.a2.1 |
| h2 | a2 | h2.a2.2 |
| h2 | a3 | h2.a3.1 |
| h3 | a2 | h3.a2.1 |
| h4 | a1 | h4.a1.1 |
| h5 | a2 | h5.a2.1 |
| h5 | a3 | h5.a3.1 |
SELECT 11
WITH
b AS MATERIALIZED
( Select lg.transaction_hash
From logs lg
Where lg.address='a2'
Group By lg.transaction_hash
LIMIT 2
)
Select hash, t_value,
( Select ARRAY_AGG( JSON_BUILD_OBJECT('address', address, 'l_value', l_value) )
From logs
Where transaction_hash = t.hash
) logs_array
From transactions t
Where t.hash IN( Select transaction_hash From b)
| hash | t_value | logs_array |
|---|---|---|
| h2 | v2 | {"{"address" : "a1", "l_value" : "h2.a1.1"}","{"address" : "a2", "l_value" : "h2.a2.1"}","{"address" : "a2", "l_value" : "h2.a2.2"}","{"address" : "a3", "l_value" : "h2.a3.1"}"} |
| h3 | v3 | {"{"address" : "a2", "l_value" : "h3.a2.1"}"} |
SELECT 2