0

I'm running a paginated SELECT ... UNION query, and I need to determine the number of pages that are in the resultset. For example, consider the query:

(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT 3

When I get the following resultset, I can determine the total number of matched records:

| id | type   | matches |
|  1 | table1 |      12 |
|  2 | table1 |      12 |
|  1 | table2 |     150 |

Summing the distinct matches columns will then give me the total number of records returned, 162. But then if my current LIMIT/OFFSET doesn't give me any records from table2, I can no longer tell how many pages of data to expect:

 | id | type   | matches |
 |  1 | table1 |      12 |
 |  2 | table1 |      12 |
 |  3 | table1 |      12 |

How can I find the total number of matched records from all clauses in a UNION even if not all clauses return records within the current LIMIT/OFFSET scope?

2
  • So your situation is that you want to display these matches in a paginated fashion, and you need to figure out how many total pages you will need to display, but yet NOT pull all matches up front? (You only want to do a offset/limit for each page when they request that page to be displayed?) – Joishi Bodio Dec 8 '15 at 23:25
  • @JoishiBodio Correct. My specific use case is a search query that returns users, groups, articles, etc. that match a given search string. But I need to know how many matches of each type are available in order to paginate properly. – Mikkel Dec 8 '15 at 23:28
1

I think you just need to change your query...

(SELECT
  NULL AS id,
  'allTables' AS type,
  (SELECT COUNT(*) OVER() FROM table1) + (SELECT COUNT(*) OVER() FROM table2) AS matches)
UNION
(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT (1 + 3)

But I can't speak to how efficient it will be....

-- Additional

This may work, as well. But again, efficiency will likely be poor (since it will get all results, regardless if you use it or not)

WITH results as (
  (SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
  UNION
  (SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
) SELECT NULL AS id, 'allTables' AS type, COUNT(matches) FROM results
UNION
SELECT id, type, matches FROM results
LIMIT (1 + 3)

Not very friendly to the DB, in either case. But you only have to have your WHERE clause twice (as opposed to 4 times)

4
  • That would require me to apply all of my WHERE conditions twice, unfortunately. Obviously, the queries in the question are oversimplified; each subquery actually has several LIKE conditions. I assume Postgres wouldn't be smart enough to know that I'm applying the same conditions to two consecutive queries. If that's the only option, running subsequent SELECT COUNT(*) queries for any tables not matched would probably be more efficient as at least the filtering logic wouldn't be duplicated for those tables that do appear in the result set. – Mikkel Dec 8 '15 at 23:36
  • Yes it would, but I (personally) don't see any other way for you to do it without either running two separate queries (one to get total and then one to get specific page) OR to pull all results up front and then simply choose what to display on each specific page from your complete set.. – Joishi Bodio Dec 8 '15 at 23:47
  • oh .. let me propose a different SQL.. – Joishi Bodio Dec 8 '15 at 23:48
  • So I guess the short answer is unions are messy and there's no good way to accomplish this. I'll run some benchmarks and post back with my results. As my data set grows I'll dump all relevant types into a single Elastic Search index and this will be moot, but I need a stopgap solution until that can be implemented. Thanks for the help; I'll wait a bit to mark this as accepted in case someone else comes along with a more elegant solution that won't kill the DB. – Mikkel Dec 8 '15 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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