How could I speed up this query?

Question

I have this query:

select 
    a.*, 
    trunc(MAX(rebase.circulating_supply) * a.supply_percentage, 9) as ampl_balance, 
    trunc(
        MAX(rebase.circulating_supply) *
        (coalesce(SUM(t.supply_percentage) FILTER (WHERE t.to = a.contract_address), 0) +
        (coalesce(SUM(t.supply_percentage) FILTER (WHERE t.from = a.contract_address), 0) * -1)),
        9
    ) as net_ampl_moved
from addresses as a
cross join (select r.circulating_supply from rebases r order by r.timestamp desc limit 1) as rebase
left join transfers as t
on (t.from = a.contract_address or t.to = a.contract_address) and t.timestamp >= ?
group by a.contract_address;

The goal here is to aggregate the net balance of an account over a certain time period e.g. from last 24 hours, all time etc. I do this by summing every transaction out and every transaction in.

Indexes on transfers:

 CREATE INDEX transfers_from_to_index ON public.transfers USING btree ("from", "to")
 CREATE INDEX transfers_timestamp_index ON public.transfers USING btree ("timestamp")
 CREATE INDEX transfers_action_index ON public.transfers USING btree (action)
 CREATE UNIQUE INDEX transfers_pkey ON public.transfers USING btree (transaction_hash, log_index)
 CREATE INDEX transfers_supply_percentage_index ON public.transfers USING btree (supply_percentage)
 CREATE INDEX transfers_amount_index ON public.transfers USING btree (amount)
 CREATE INDEX transfers_supply_percentage_timestamp_log_index_index ON public.transfers USING btree (supply_percentage, "timestamp", log_index)
 CREATE INDEX transfers_from_index ON public.transfers USING btree ("from")
 CREATE INDEX transfers_to_index ON public.transfers USING btree ("to")

Indexes on addresses:

 CREATE UNIQUE INDEX addresses_pkey ON public.addresses USING btree (contract_address)
 CREATE INDEX addresses_supply_percentage_index ON public.addresses USING btree (supply_percentage)

Indexes on rebase:

 CREATE UNIQUE INDEX rebases_pkey ON public.rebases USING btree (epoch)
 CREATE INDEX rebases_timestamp_index ON public.rebases USING btree ("timestamp")

Explain plan:

https://explain.depesz.com/s/1Zy

Any help here would be greatly appreciated.

Answer 1

The left join on transfers is the most costly part, largely due to the OR and there being no good index to solve it.

Mybe try this?

select 
    a.*, 
    trunc(
        MAX(rebase.circulating_supply)
          * a.supply_percentage,
        9
    ) as ampl_balance, 
    trunc(
        MAX(rebase.circulating_supply)
          * coalesce(SUM(t.supply_percentage), 0),
        9
    ) as net_ampl_moved
from addresses as a
cross join (select r.circulating_supply from rebases r order by r.timestamp desc limit 1) as rebase
left join (
  select tt.to   as contract_address, tt.timestamp,  tt.supply_percentage from transfers tt
  union all
  select tf.from as contract_address, tf.timestamp, -tf.supply_percentage from transfers tf
) t
on t.contract_address = a.contract_address and t.timestamp >= ?
group by a.contract_address;

Then have two new indexes in transfers...

CREATE INDEX transfers_to_timestamp_index
  ON public.transfers
  USING btree ("to", "timestamp")
  INCLUDE ("supply_percentage");

CREATE INDEX transfers_from_timestamp_index
  ON public.transfers
  USING btree ("from", "timestamp")
  INCLUDE ("supply_percentage");

That should make both the join and the aggregate cheaper.

EDIT: An alternative...

Using the same indexes as mentioned above, but collapsing all the aggregation to a sub query on just the transfers table.

(And moving the sub-queries to CTEs for readability...)

WITH
  t_normalised
AS
(
  select tt.to   as contract_address, tt.timestamp,  tt.supply_percentage from transfers tt
  union all
  select tf.from as contract_address, tf.timestamp, -tf.supply_percentage from transfers tf
),
  t_sum
AS
(
  SELECT
    contract_address,
    SUM(supply_percentage)  AS supply_percentage
  FROM
    t_normalised
  WHERE
    timestamp >= ?
  GROUP BY
    contract_address
),
  rebase
AS
(
  select r.circulating_supply
    from rebases r
order by r.timestamp desc
   limit 1
) 
select 
    a.*, 
    trunc(
        rebase.circulating_supply
          * a.supply_percentage,
        9
    ) as ampl_balance, 
    trunc(
        rebase.circulating_supply
          * coalesce(t_sum.supply_percentage, 0),
        9
    ) as net_ampl_moved
from
  addresses as a
cross join
  rebase
left join
  t_sum
    on t_sum.contract_address = a.contract_address