1

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?

3
  • 2
    Start with an EXPLAIN (ANALYZE, BUFFERS) for the query. Turn track_io_timing on first if not already one.
    – jjanes
    Nov 17, 2023 at 2:02
  • What is the returned count?
    – bobflux
    Nov 18, 2023 at 22:58
  • I found a solution, see below. But I also posted a follow up question to help me explain why this query must be slow: dba.stackexchange.com/questions/333704/…
    – bdadam
    Dec 3, 2023 at 22:51

2 Answers 2

3

count() is no slower than other aggregates. But I don't understand how people can assume it to be fast. Counting the socks in your drawer isn't fast either, if you got lots of them. See here for the available options to speed up counts.

Anyway, whenever somebody complains about a query like that to be slow, I jump to the conclusion that they are calculating a total result set count. That is always a terrible idea, and the solution is don't do it. Choose one of the available alternatives:

  • don't display a total result set count at all
  • don't display a total result set count right away, but give the user a button to calculate it if they really want it and are ready to wait for it
  • use EXPLAIN to get an approximate count quickly
1
  • Hey Laurenz, thanks for the answer. Unfortunately the proposed solutions (not displaying and lazy loading) were not acceptable for this product. The numbers returned by EXPLAIN were way too far off. Also the amount of data is so miniscule that it does not warrant such a slow search. I just posted the solution I ended up with. Of course it needed restructuring of the DB schema, which was totally fine in this case.
    – bdadam
    Dec 3, 2023 at 22:34
0

I knew it is possible to speed things up and make this fast. The amount of data is miniscule, ca. 1GB with indices and everything together. Only my schema was not adequate for the type of queries I need. Here is the solution I ended up with.

Flatten the matrix

I restructured the calculation table in the following way and renamed it to financialData and "flattened" the calculation matrix. Each calculation became just a single value in a column.

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)

In the financialData table the ID is the same as the listing's ID. The other columns hold the monthly rate for a given combination of parameters, e.g. rate_12_10_25 contains the monthly rate of 12 moths, 10% downpayment 25% rest payment.

How to query this table?

This ended up more straightforward than I initially thought.

Example from the initial question: find all listings that have color ID 7 and less than 75000 miles and also 0% downPayment with 25% final payment and a monthly rate below 350$ and order them by monthly rate. (Please note that I'm interested in all the listings and not in all the possible financial offers.)

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

The corresponding COUNT query is even simpler:

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
  )

The solution needs some smartness in the application code that chooses the right columns (rate_AA_BB_CC) to query. Depending on the user's search criteria a dynamic number of rate_AA_BB_CC fields are included in the query, e.g. if the user is only interested in 12 month contracts, then only rate_12_00_00, rate_12_00_25, rate_12_10_00, rate_12_10_25 columns are chosen.

After restructuring the schema I could achieve sub 100ms execution time for most queries.

Addendum You might have noticed that in the financialData table I added a column offers jsonb. This simply stores some other details about the financial offer that are not intended to be searchable but might need to be shown to the user (legal things, other fees, etc.). This field is simply processed by application code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.