Início / dba / Perguntas / 333193
Accepted
bdadam
Asked: 2023-11-17 07:21:50 +0800 CST

Consulta COUNT extremamente lenta no Aurora Postgress Serverless v2 com duas tabelas

  • 772

Estou projetando um aplicativo web onde os vendedores podem oferecer seus carros, os bancos oferecem diversas ofertas de financiamento (por exemplo, 36 meses, 25% de entrada, 25% de pagamento final). Os compradores acessam este aplicativo da web e procuram um carro - com base em vários critérios de pesquisa: por exemplo, menos de 5 anos, o pagamento mensal é inferior a 500$, carros vermelhos que custam mensalmente abaixo de 350$ com uma duração de contrato de 36 ou 48 meses.

No meu sistema tenho listagens e cada listagem pode ter até 18 cálculos .

Uma listagem é um carro. Para resumir, uma listagem tem os seguintes atributos: id, cor, quilometragem.

Um cálculo é uma oferta de financiamento. Cada cálculo possui os seguintes atributos: id, listId, financeProviderId, meses, downPayment, finalPayment, mensalRate.

No BD tenho duas tabelas: listagem e cálculo.

CREATE TABLE IF NOT EXISTS public.calculation
(
    id uuid NOT NULL,
    "listingId" uuid NOT NULL,
    "financeProviderId" smallint NOT NULL,
    "downPayment" numeric(10,2) NOT NULL,
    "finalTerm" numeric(10,2) NOT NULL,
    rate numeric(10,2),
    CONSTRAINT calculation_pkey PRIMARY KEY (id),
    CONSTRAINT "calculation_listingId_fkey" FOREIGN KEY ("listingId")
        REFERENCES public.listing (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

CREATE INDEX IF NOT EXISTS "calculation_listingId"
    ON public.calculation USING btree
    ("listingId" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS "calculation_downPayment"
    ON public.calculation USING btree
    ("downPayment" ASC NULLS LAST)
    TABLESPACE pg_default;

-- similar indices for all the other fields

CREATE TABLE IF NOT EXISTS public.listing
(
    id uuid NOT NULL,
    color integer,
    mileage integer,
    CONSTRAINT listing_pkey PRIMARY KEY (id)
)

CREATE INDEX IF NOT EXISTS listing_mileage
    ON public.listing USING btree
    (mileage ASC NULLS LAST)
    TABLESPACE pg_default;
-- similar indices for constructionYear and other attributes

Quando os usuários procuram um carro para comprar, eles desejam ver uma lista paginada de carros que atendem aos seus critérios de pesquisa e também o número total de carros correspondentes.

Obter a lista geralmente não é um problema, porque uma página de lista mostra apenas no máximo 20 carros.

MAS cada consulta COUNT é extremamente lenta (2 a 20 segundos), embora ainda não haja carga no banco de dados (o produto está antes do lançamento). Aqui está uma consulta que deseja contar o número de listagens que possuem ID de cor 7 e menos de 75.000 milhas e também 0% de entrada com 25% de pagamento final e uma taxa mensal abaixo de 350$.

SELECT COUNT(DISTINCT "l"."id")
FROM "listing" as "l" 
INNER JOIN "calculation" as "ca" ON "l"."id" = "ca"."listingId"
WHERE
  "l"."color" = 7 AND "mileage" < 75000
  AND "ca"."downPayment" = 0 AND "ca"."finalTerm" = 25 AND monthlyRate < 350

No sistema eu tenho ca. 300 mil listagens e 1,5 milhão de cálculos. (Nem toda listagem tem todos os 18 cálculos possíveis, por exemplo, carros mais antigos não recebem ofertas por 60 ou 72 meses.)

Estou usando o AWS Aurora Postgres Serverless V2. Mas acho que consultas COUNT lentas são um problema geral do Postgres. Também estou bastante surpreso que uma quantidade tão pequena de dados já possa causar um desempenho tão ruim.

Agora estou perguntando o que posso fazer para acelerar a consulta de contagem. Meu objetivo seria fazer com que a consulta COUNT fosse executada abaixo de 100 ms, mas eu poderia viver com menos de 350 ms.

Existe um segredo para consultas COUNT rápidas no Postgres?

postgresql

2 respostas

  1. count()não é mais lento que outros agregados. Mas não entendo como as pessoas podem presumir que isso seja rápido. Contar as meias da gaveta também não é rápido, se você tiver muitas. Veja aqui as opções disponíveis para agilizar as contagens.

    De qualquer forma, sempre que alguém reclama que uma consulta como essa é lenta, chego à conclusão de que eles estão calculando uma contagem total do conjunto de resultados . Essa é sempre uma péssima ideia, e a solução é não fazer isso . Escolha uma das alternativas disponíveis:

    • não exibe uma contagem total do conjunto de resultados
    • não exibe uma contagem total do conjunto de resultados imediatamente, mas fornece ao usuário um botão para calculá-la se ele realmente quiser e estiver pronto para esperar por isso
    • use EXPLAINpara obter uma contagem aproximada rapidamente
    • 3
  2. Best Answer

    Eu sabia que é possível acelerar as coisas e fazer isso rápido. A quantidade de dados é minúscula, ca. 1GB com índices e tudo junto. Apenas meu esquema não era adequado para o tipo de consulta que preciso. Aqui está a solução que encontrei.

    Achate a matriz

    Reestruturei a calculationtabela da seguinte maneira, renomeei-a financialDatae "achatei" a matriz de cálculo. Cada cálculo tornou-se apenas um único valor em uma coluna.

    CREATE TABLE IF NOT EXISTS public."financialData"
(
    id uuid NOT NULL,
    rate_12_10_25 numeric(10,2),
    rate_12_10_00 numeric(10,2),
    rate_12_00_25 numeric(10,2),
    rate_12_00_00 numeric(10,2),
    rate_24_10_25 numeric(10,2),
    rate_24_10_00 numeric(10,2),
    rate_24_00_25 numeric(10,2),
    rate_24_00_00 numeric(10,2),
    rate_36_10_25 numeric(10,2),
    rate_36_10_00 numeric(10,2),
    rate_36_00_25 numeric(10,2),
    rate_36_00_00 numeric(10,2),
    rate_48_10_25 numeric(10,2),
    rate_48_10_00 numeric(10,2),
    rate_48_00_25 numeric(10,2),
    rate_48_00_00 numeric(10,2),
    rate_60_10_25 numeric(10,2),
    rate_60_10_00 numeric(10,2),
    rate_60_00_25 numeric(10,2),
    rate_60_00_00 numeric(10,2),
    rate_72_10_25 numeric(10,2),
    rate_72_10_00 numeric(10,2),
    rate_72_00_25 numeric(10,2),
    rate_72_00_00 numeric(10,2),
    offers jsonb,
    "createdAt" timestamp with time zone NOT NULL DEFAULT now(),
    "updatedAt" timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT "financialData_pkey" PRIMARY KEY (id)
)

CREATE INDEX IF NOT EXISTS "financialData_all_rates"
    ON public."financialData" USING btree
    (rate_12_10_25 ASC NULLS LAST, rate_12_10_00 ASC NULLS LAST, rate_12_00_25 ASC NULLS LAST, rate_12_00_00 ASC NULLS LAST, rate_24_10_25 ASC NULLS LAST, rate_24_10_00 ASC NULLS LAST, rate_24_00_25 ASC NULLS LAST, rate_24_00_00 ASC NULLS LAST, rate_36_10_25 ASC NULLS LAST, rate_36_10_00 ASC NULLS LAST, rate_36_00_25 ASC NULLS LAST, rate_36_00_00 ASC NULLS LAST, rate_48_10_25 ASC NULLS LAST, rate_48_10_00 ASC NULLS LAST, rate_48_00_25 ASC NULLS LAST, rate_48_00_00 ASC NULLS LAST, rate_60_10_25 ASC NULLS LAST, rate_60_10_00 ASC NULLS LAST, rate_60_00_25 ASC NULLS LAST, rate_60_00_00 ASC NULLS LAST, rate_72_10_25 ASC NULLS LAST, rate_72_10_00 ASC NULLS LAST, rate_72_00_25 ASC NULLS LAST, rate_72_00_00 ASC NULLS LAST)

    Na financialDatatabela o ID é igual ao ID da listagem. As outras colunas contêm a taxa mensal para uma determinada combinação de parâmetros, por exemplo, rate_12_10_25contém a taxa mensal de 12 meses, 10% de entrada e 25% de restante.

    Como consultar esta tabela?

    Isso acabou sendo mais simples do que eu pensava inicialmente.

    Exemplo da pergunta inicial: encontre todos os anúncios que tenham ID de cor 7 e menos de 75.000 milhas e também 0% de entrada com pagamento final de 25% e taxa mensal inferior a 350$ e ordene-os por taxa mensal. (Observe que estou interessado em todas listingse não em todas as ofertas financeiras possíveis.)

    SELECT LEAST(rate_12_00_25,rate_24_00_25,rate_36_00_25,rate_48_00_25,rate_60_00_25) as min_rate, * from "listing"
INNER JOIN "financialData" ON "financialData"."id" = "listing"."id"
WHERE "color" = 7 AND "mileage" < 75000
  AND (
    "rate_12_00_25" < 350 OR
    "rate_24_00_25" < 350 OR
    "rate_36_00_25" < 350 OR
    "rate_48_00_25" < 350 OR
    "rate_60_00_25" < 350
  )
ORDER BY min_rate

    A consulta correspondente COUNTé ainda mais simples:

    SELECT COUNT(*) from "listing"
INNER JOIN "financialData" ON "financialData"."id" = "listing"."id"
WHERE "color" = 7 AND "mileage" < 75000
  AND (
    "rate_12_00_25" < 350 OR
    "rate_24_00_25" < 350 OR
    "rate_36_00_25" < 350 OR
    "rate_48_00_25" < 350 OR
    "rate_60_00_25" < 350
  )

    A solução precisa de alguma inteligência no código do aplicativo que escolhe as colunas corretas ( rate_AA_BB_CC) para consultar. Dependendo dos critérios de pesquisa do usuário, um número dinâmico de rate_AA_BB_CCcampos é incluído na consulta, por exemplo, se o usuário estiver interessado apenas em contratos de 12 meses, apenas as colunas rate_12_00_00, rate_12_00_25, rate_12_10_00, rate_12_10_25serão escolhidas.

    Depois de reestruturar o esquema, consegui atingir um tempo de execução inferior a 100 ms para a maioria das consultas.

    Adendo Você deve ter notado que na financialDatatabela adicionei uma coluna offers jsonb. Isto simplesmente armazena alguns outros detalhes sobre a oferta financeira que não se destinam a ser pesquisados, mas que podem precisar ser mostrados ao usuário (coisas legais, outras taxas, etc.). Este campo é simplesmente processado pelo código do aplicativo.

    • 0

relate perguntas