Counting number of records newer than X date matching a condition quickly

Question

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.

Answer 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';