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?