2

I have a production application that utilizes postgres. A common pattern we use is to:

A) Formulate the query necessary to select the data with a given arbitrary set of filters that the user selects from the UX layer. These queries are usually very complex and heavily optimized to be fast. For the sake of this example lets call it SELECT * FROM real_query WHERE complicated = true

B) The application then takes this core query and dispatches 2 separate queries based on the core query. The first simply appends a reasonable LIMIT/OFFSET to only fetch a page worth of records. The second will wrap the original query in SELECT count(*) FROM ( <<ORIGINAL QUERY>>)t in order to get to total count of records (without the limit/offset) which is necessary for the paginated UX.

With that out of the way here is my real question. The original query is very fast for it's complexity. Roughly 400ms. However as soon as we wrap it with the count operation it slows down to 20 seconds. It's as if the subquery count version ignores all the optimization and indexes we used to make the core query fast.

So why is SELECT * FROM real_query WHERE complicated = true fast, but SELECT count(*) FROM (SELECT * FROM real_query WHERE complicated = true)t so slow?

What can I do to make the counting query faster?

5
  • 1
    Please consider reading this advice
    – mustaccio
    Oct 6, 2022 at 17:56
  • This PostgreSQL Wiki article explains the issue and provides a potential solution: wiki.postgresql.org/wiki/Count_estimate
    – dwhitemv
    Oct 6, 2022 at 19:30
  • 2
    Please edit your question and add the execution plan of both queries generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan.
    – user1822
    Oct 6, 2022 at 20:20
  • 2
    How can we explain the inner workings of something we aren't even allowed to see?
    – jjanes
    Oct 6, 2022 at 20:22
  • 1
    You can anonymise your EXPLAIN... at depesz.com
    – Vérace
    Oct 6, 2022 at 21:13

1 Answer 1

1

You can confirm this by looking at your execution plans, but this is a fairly normal problem with a very predictable reason.

Your paginated query only needs to do the work to return the first set of rows. Your count query needs to count every row that matches your filters. If some of your filters require table row access then you are now visiting every row that matches the filter rather than just enough to populate a page. This can be much slower.

If you know your indexes and are happy making predictions about your data then you can run different queries to return the counts only using indexed columns in your filters, and then scale that back depending on how you expect the other filters to work.

Otherwise, decide whether you really need to get the full count of rows. Do people care if there’s 45,121 rows or do they just care there’s another page of results?

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.