0

I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions.

Here is basically what I'm working with:

CREATE TABLE public.transactions (
    "timestamp" timestamptz NOT NULL,
    tx varchar(255) NOT NULL,
    account varchar(255) NOT NULL
);

CREATE INDEX transactions_account_timestamp_idx ON public.transactions USING btree (account, "timestamp" DESC);
CREATE INDEX transactions_timestamp_idx ON public.transactions USING btree ("timestamp" DESC);
CREATE UNIQUE INDEX unique_transactions ON public.transactions USING btree ("timestamp", account, tx);

I would like to be able to keep an up to date record of all the transactions for each account based on different timeframes:

IE: provide number of tx for account 'abc' in last hour, provide number of tx for account 'abc' in last year.

select count(*) from transactions where account = 'abc' and "timestamp" >= date_trunc('hour', now());

select count(*) from transactions where account = 'abc' and "timestamp" >= date_trunc('year', now());

The problem is count(*) is incredibly slow and I need to update the # of transactions for each possible account once every 10-15 minutes or so. I'm unable to drop indexes as this needs to be an ongoing operation.

4
  • Maybe this helps.
    – McNets
    Oct 9, 2021 at 17:39
  • 1
    Could you share an Explain? Preferably explain (analyze, buffers).
    – jkavalik
    Oct 9, 2021 at 18:06
  • 1
    You need an index on (account, timestamp) Oct 9, 2021 at 19:12
  • Vacuum the table first. If that doesn't fix the problem, show an explain (analyze, buffers)
    – jjanes
    Oct 9, 2021 at 21:13

1 Answer 1

1

This would go twice as fast, or half as slow:

select
  sum(
    case when "timestamp" >= date_trunc('hour', now()) then 1 else 0 end
   ) txct_lasthour,
  sum(
    case when "timestamp" >= date_trunc('year', now()) then 1 else 0 end
  ) txct_lastyear
from transactions where account = 'abc';
2
  • Mind-blowingly, it might also be able to go twice as fast and half as slow at the same time
    – Andriy M
    Oct 11, 2021 at 13:58
  • Ah, I dropped out of school before we got to quantum mechanics. Oct 11, 2021 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.