Postgres 15

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 transcation_table
(
    id                       int,
    date_time                date,
    sender_wallet            int,
    amount_sent              int,
    sender_updated_balance   int,
    receiver_wallet          int,
    amount_received          int,
    receiver_updated_balance int
);
create table wallet_table
(
    id       int,
    userid   int,
    wallet   text,
    currency text,
    balance  int
);
create table user_table
(
    id    int,
    uname text
);

insert into user_table (id, uname)
VALUES (1, 'u1'),
       (2, 'u2'),
       (3, 'u3'),
       (4, 'u4'),
       (5, 'u5');
insert into wallet_table (id, userid, wallet, currency, balance)
VALUES (1, 1, 'w1', 'usd', 1000),
       (2, 2, 'w1', 'usd', 1000),
       (3, 3, 'w1', 'usd', 1000),
       (4, 4, 'w1', 'usd', 1000),
       (5, 5, 'w1', 'usd', 1000),
       (6, 1, 'w2', 'usd', 1000),
       (7, 2, 'w2', 'usd', 1000);

insert into transcation_table (id, date_time, sender_wallet, amount_sent, sender_updated_balance, receiver_wallet,
                               amount_received, receiver_updated_balance)
VALUES (1, '2023/01/01', 1, 100, 900, 2, 100, 1100),
       (2, '2023/01/03', 1, 100, 800, 3, 100, 1100),
       (3, '2023/01/04', 4, 100, 900, 1, 100, 900),
       (4, '2023/01/05', 1, 100, 800, 5, 100, 1100);

select t.id,
       t.date_time,
       su.uname as sender_uname,
       t.amount_sent,
       t.sender_updated_balance,
       ru.uname as receiver_uname,
       t.amount_received,
       t.receiver_updated_balance
from transcation_table t
         inner join wallet_table sw on t.sender_wallet = sw.id
         inner join wallet_table rw on t.receiver_wallet = rw.id
         inner join user_table su on su.id = sw.userid
         inner join user_table ru on ru.id = rw.userid
where su.id = 1
   or ru.id = 1
order by t.date_time;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 5

INSERT 0 7

INSERT 0 4

id	date_time	sender_uname	amount_sent	sender_updated_balance	receiver_uname	amount_received	receiver_updated_balance
1	2023-01-01	u1	100	900	u2	100	1100
2	2023-01-03	u1	100	800	u3	100	1100
3	2023-01-04	u4	100	900	u1	100	900
4	2023-01-05	u1	100	800	u5	100	1100

SELECT 4