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 '21 at 17:39
  • 1
    Could you share an Explain? Preferably explain (analyze, buffers).
    – jkavalik
    Oct 9 '21 at 18:06
  • 1
    You need an index on (account, timestamp) Oct 9 '21 at 19:12
  • Vacuum the table first. If that doesn't fix the problem, show an explain (analyze, buffers)
    – jjanes
    Oct 9 '21 at 21:13
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 '21 at 13:58
  • Ah, I dropped out of school before we got to quantum mechanics. Oct 11 '21 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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