Otimizando uma consulta agregada no postgres

Gostaria da sua ajuda para otimizar uma consulta cada vez mais lenta - ou melhor ainda, me ajude a entender qual é o problema e me indique a direção certa.

Todos os dias eu faço scraping de 3 supermercados e registro os preços deles em um aplicativo django. Cada scraping resulta em um novo Retrievalregistro, alguns milhares Pricede registros e talvez em alguns Itemregistros (a maioria dos itens já existe, então eu registro apenas os preços deles para um determinado dia).

Uma vez feito isso, um processo é executado que calcula as mudanças de preço para todos os itens de hoje, recuperando os dois últimos preços de cada item. Esses dois últimos preços podem não ser sempre em dias consecutivos, porque alguns itens vêm e vão.

As 3 tabelas nas quais estamos interessados ​​e seus índices são assim:

CREATE TABLE public.main_item (
    id uuid NOT NULL,
    name character varying(512) NOT NULL
);

CREATE TABLE public.main_price (
    id uuid NOT NULL,
    per_item double precision NOT NULL,
    item_id uuid NOT NULL,
    retrieval_id uuid NOT NULL
);

CREATE TABLE public.main_retrieval (
    id uuid NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
);

CREATE INDEX name_index ON public.main_item USING btree (name);
CREATE INDEX timestamp_index ON public.main_retrieval USING btree ("timestamp");

Tenho uma consulta que retorna um resultado parecido com este, onde idestá o Itemid e latest_pricesum json que contém os últimos 2 preços daquele item e então processo os resultados em python.

No entanto, a consulta está ficando cada vez mais lenta. Minha suposição é por causa do tamanho da Pricetabela, que agora é de ~16 milhões de linhas.

Executando uma EXPLAIN ANALYZEconsulta, vejo que a maior parte do tempo é gasto em uma consulta Bitmap Heap Scanna main_pricetabela: https://explain.depesz.com/s/ZX78#stats

Abaixo você pode encontrar a consulta que o django ORM gerou para hoje:

SELECT "main_item"."id", ARRAY(
    SELECT JSONB_BUILD_OBJECT(('price_id')::text, U0."id", ('per_item')::text, U0."per_item") AS "json"
    FROM "main_price" U0
        INNER JOIN "main_retrieval" U2
        ON (U0."retrieval_id" = U2."id")
    WHERE (U0."item_id" = ("main_item"."id") AND U2."timestamp" < '2024-09-24 00:00:00+00:00')
    ORDER BY U2."timestamp" DESC LIMIT 2
    ) AS "latest_prices"

    FROM "main_item"
    WHERE "main_item"."id" IN (
        SELECT V1."item_id"
        FROM "main_retrieval" V0
            LEFT OUTER JOIN "main_price" V1
            ON (V0."id" = V1."retrieval_id")
        WHERE (
            V0."timestamp" >= '2024-09-23 00:00:00+00:00'
            AND V0."timestamp" < '2024-09-24 00:00:00+00:00'
            AND NOT (
                EXISTS(
                    SELECT 1 AS "a"
                    FROM "main_retrieval" U0
                        LEFT OUTER JOIN "main_price" U1
                        ON (U0."id" = U1."retrieval_id")
                    WHERE (U1."item_id" IS NULL AND U0."id" = (V0."id"))
                    LIMIT 1))))

Observação

Ficarei mais do que feliz em abandonar a consulta gerada pelo ORM e escrevê-la manualmente.

No entanto, estou lutando para encontrar uma maneira ótima de buscar os 2 preços mais recentes para cada Item. Buscar o mais recente é fácil, mas buscar o anterior é surpreendentemente difícil.

Qualquer ajuda é muito apreciada. Obrigado antecipadamente.