Início / user-282045

bdadam's questions

Martin Hope
bdadam
Asked: 2023-11-17 07:21:50 +0800 CST
  • 6

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