I have a table with multiple boolean flags and I want to count how many of the flags are set.
Currently I use one query per flag, like this...
SELECT COUNT(*) FROM tableA WHERE flag1 = true;
SELECT COUNT(*) FROM tableA WHERE flag2 = true;
...
...
SELECT COUNT(*) FROM tableA WHERE flagN = true;
I'd like to combine them into a single query, like this...
SELECT
SUM((CASE WHEN flag1 = FALSE THEN 0 ELSE 1 END)) AS cnt1,
SUM((CASE WHEN flag2 = FALSE THEN 0 ELSE 1 END)) AS cnt2,
...
...
SUM((CASE WHEN flagN = FALSE THEN 0 ELSE 1 END)) AS cntN,
FROM
tableA
The combined query works, but it takes about 12 times longer than ALL of the individual queries combined. All the flags are indexed the same, why does the combined query take so long to run and is there a way to re-write it take less time?
Performance is often proportional to the number of examined rows.
The latter query is bound to do a table-scan, i.e. examine every row in the table.
The individual queries reduce the set of examined rows by using indexes on the respective flag columns. If the subset of rows where each flag is true is small enough, then the total number of examined rows may be a lot lower than the rows examined during a table-scan.
Of course I'm making a few assumptions, but as you commented above, it may turn out to be accurate in your case.
is there a way to re-write it take less time?
Not as a single query. I'd stick with the multiple individual queries since the total time is so much less given your data.
This would change if the number of rows matching your flag conditions were large, so the cost of multiple individual queries would result in more total rows examined than a single table-scan.