I'm designing a web application where sellers can offer their cars, banks provide various financing offers (e.g. 36 months, 25% downpayment, 25% final payment). Buyers come to this web app and they search for a car - based on various search criteria: e.g. younger than 5 years, monthly payment is below 500$, red cars that costs monthly below 350$ with a contract duration of 36 or 48 months.
In my system I have listings and each listing might have up to 18 calculations.
A listing is a car. For brevity, a listing has the following attributes: id, color, mileage.
A calculation is a financing offer. Each calculation has the following attributes: id, listingId, financeProviderId, months, downPayment, finalPayment, monthlyRate.
In the DB I have two tables: listing and calculation.
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
When users are searching for a car to buy, they want to see a paginated list of cars that fit their search criteria and also the total number of matching cars.
Getting the list is usually not a problem, because a list page only shows a maximum of 20 cars.
BUT every COUNT query is extremely slow (2-20 seconds) although there is no load on the database yet (product is before release). Here is one such query that wants to count the number of listings that have a color ID 7 and less than 75000 miles and also 0% downPayment with 25% final payment and a monthly rate below 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
In the system I have ca. 300K listings and 1.5M calculations. (Not every listing has all 18 possible calculations, e.g. older cars don't get offers for 60 or 72 months.)
I'm using AWS Aurora Postgres Serverless V2. But I guess slow COUNT queries is a general Postgres issue. Also I'm quite surprized that such a small amount of data can already cause such a bad performance.
Now I'm asking what could I do to speed up the count query. My goal would be to have the COUNT query run below 100ms but I could live with below 350ms.
Is there a secret to fast COUNT queries on Postgres?
EXPLAIN (ANALYZE, BUFFERS)for the query. Turn track_io_timing on first if not already one.