PostgreSQL - How to optimize slow Bitmap Heap Scan?

Question

My app is calculating the SuperTrend indicator over the price of multiple cryptocurrencies. I have the following table and indices:

-- DDL generated by Postico 2.1.1
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE "SuperTrend" (
    id integer DEFAULT nextval('"SuperTrend_id_seq"'::regclass) PRIMARY KEY,
    "coinId" character varying(255) NOT NULL REFERENCES "Coin"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    "quoteSymbol" text,
    date timestamp(3) without time zone NOT NULL,
    trend text NOT NULL,
    flavor text NOT NULL,
    interval text NOT NULL DEFAULT '1d'::text,
    streak integer NOT NULL DEFAULT 0
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "SuperTrend_pkey" ON "SuperTrend"(id int4_ops);
CREATE INDEX "SuperTrend_flavor_interval_date_idx" ON "SuperTrend"(flavor text_ops,interval text_ops,date timestamp_ops);
CREATE UNIQUE INDEX "SuperTrend_coinId_quoteSymbol_date_flavor_interval_key" ON "SuperTrend"("coinId" text_ops,"quoteSymbol" text_ops,date timestamp_ops,flavor text_ops,interval text_ops) NULLS NOT DISTINCT;
CREATE INDEX "SuperTrend_quoteSymbol_flavor_interval_idx" ON "SuperTrend"("quoteSymbol" text_ops,flavor text_ops,interval text_ops);

I want to know the newest "SuperTrend" for a particular coinId, flavor and interval, for each quoteSymbol, so this is the query I'm running:

SELECT DISTINCT ON ("quoteSymbol") "trend", "quoteSymbol", "streak", "date", "flavor", "interval"
    FROM "SuperTrend"
    WHERE "coinId" = 'solana'
    AND "flavor" = 'Classic'
    AND "interval" = '1h'
    ORDER BY "quoteSymbol", date DESC;

An example result of this query would look like this:

trend   quoteSymbol streak  date    flavor  interval
UP  btc 1   2024-07-11 16:00:00 Classic 1h
DOWN    usd 1   2024-07-11 18:00:00 Classic 1h
DOWN    NULL    1   2024-07-11 18:00:00 Classic 1h // NULL has a special meaning, it means this is the overall Supertrend of this coin vs Bitcoin and vs USD

Now, trying to run this query over multiple thousands of coins and multiple intervals and multiple flavors becomes kind of slow.

There are a couple of reasons why I see it could be slow:

• Network overhead adds to time on every query. This is true but can be optimized with using the right client (pipelining, etc.) plus its still slow trying this on my local machine.
• The query could probably be optimized
• The indices I'm using are for some reason not ideal. Here is what EXPLAIN ANALYZE gives me on my local machine:

Unique  (cost=12754.96..12771.11 rows=3 width=33) (actual time=306.074..306.296 rows=4 loops=1)
  ->  Sort  (cost=12754.96..12763.03 rows=3230 width=33) (actual time=306.074..306.156 rows=4971 loops=1)
        Sort Key: "quoteSymbol", date DESC
        Sort Method: quicksort  Memory: 503kB
        ->  Bitmap Heap Scan on "SuperTrend"  (cost=524.31..12566.69 rows=3230 width=33) (actual time=8.729..301.113 rows=4971 loops=1)
              Recheck Cond: ((("coinId")::text = 'solana'::text) AND (flavor = 'Classic'::text) AND ("interval" = '1h'::text))
              Heap Blocks: exact=2218
              ->  Bitmap Index Scan on "SuperTrend_coinId_quoteSymbol_date_flavor_interval_key"  (cost=0.00..523.50 rows=3230 width=0) (actual time=8.480..8.481 rows=4980 loops=1)
                    Index Cond: ((("coinId")::text = 'solana'::text) AND (flavor = 'Classic'::text) AND ("interval" = '1h'::text))
Planning Time: 0.180 ms
Execution Time: 306.348 ms

300ms for one query is slow when you have to run many thousand queries this way. It gets a lot faster when you run the same query again, but that doesn't help here.

I'm no expert, but it seems it's just running one loop of every operation here and the Recheck condition is probably not even done because there are no lossy matches, just exact ones. It seems the main cost comes from the Bitmap Heap Scan, but I'm not sure what can be done about that.

So my question is, is there a better way to run these queries? Or are my indices suboptimal?

Answer 1

I solved this with help of my AI friend. Adding the following index helped:

CREATE INDEX ON "SuperTrend"("coinId", "flavor", "interval", "quoteSymbol", "date" DESC) INCLUDE ("trend", "streak");

My understanding is there are 2 speed improvements here:

1. Sorting the data DESC in the index, so the newest SuperTrend can be found quicker
2. Making this a covering index is supposedly helping too. But I'm not sure if that's the case, so if somebody could explain what AI is spitting out here it would be great.