1

I have a postgres 9.6 table called selected_media, with a column called _type that is set to either book or movie.

I want to select the COUNT of each type, in 1 single row, like this:

25 | 715

To do this, I have this query:

SELECT
  COALESCE(SUM(CASE WHEN _type = 'book'  THEN 1 ELSE 0 END), 0) AS books_count,
  COALESCE(SUM(CASE WHEN _type = 'movie' THEN 1 ELSE 0 END), 0) AS movies_count
FROM selected_media
WHERE subscriber_id = $1

The problem is 2 fold:

  1. I want each column to show the respective total count for that _type only if the sum in that column happens to be 0 (none selected). This is because the UI/UX states "selecting 0 books or movies is the same as selecting all of them".

  2. It needs to be fast, this table will have millions of rows in it

3

Then do it slightly different, use the (more or less) complementary of COALESCE, which is NULLIF:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here

If you don't want to have a NULL value when the result is zero, but another one, you can use:

SELECT
    CASE 
    WHEN books_count <> 0 THEN books_count 
    ELSE (another_value_or_expression_for_books) 
    END  AS books_count,
    CASE 
    WHEN movies_count <> 0 THEN movies_count 
    ELSE (another_value_or_expression_for_movies) 
    END  AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;

You can also use count(case ...) instead of SUM, and, in latest versions of PostgreSQL, you can use a FILTER, instead of CASE:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    (SELECT
        count(*) FILTER (WHERE _type = 'book') AS books_count,
        count(*) FILTER (WHERE _type = 'movie') AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here


If you need the fastest possible speed, and depending on the number of _types, and the number of elements of each type, you could use some partial indexes:

CREATE INDEX idx_books ON selected_media (subscriber_id) WHERE _type='book' ;
CREATE INDEX idx_movies ON selected_media (subscriber_id) WHERE _type='movie' ;

And then perform your query as two subqueries:

SELECT
    nullif( (select count(*) from selected_media where _type='book'  
             and subscriber_id = 1234), 
           0) AS count_books,
    nullif( (select count(*) from selected_media where _type='movie'  
             and subscriber_id = 1234), 
           0) AS count_movies ;

This will give you a query plan doing two index only scans most of the time.

| QUERY PLAN                                                                                                                                                    |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Result  (cost=164.10..164.12 rows=1 width=16) (actual time=1.491..1.492 rows=1 loops=1)                                                                       |
|   InitPlan 1 (returns $0)                                                                                                                                     |
|     ->  Aggregate  (cost=159.94..159.95 rows=1 width=8) (actual time=1.485..1.485 rows=1 loops=1)                                                             |
|           ->  Index Only Scan using idx_books on selected_media  (cost=0.28..150.36 rows=3833 width=0) (actual time=0.016..1.108 rows=3974 loops=1)           |
|                 Index Cond: (subscriber_id = 1234)                                                                                                            |
|                 Heap Fetches: 3974                                                                                                                            |
|   InitPlan 2 (returns $1)                                                                                                                                     |
|     ->  Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)                                                                 |
|           ->  Index Only Scan using idx_movies on selected_media selected_media_1  (cost=0.12..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) |
|                 Index Cond: (subscriber_id = 1234)                                                                                                            |
|                 Heap Fetches: 0                                                                                                                               |
| Planning time: 0.094 ms                                                                                                                                       |
| Execution time: 1.514 ms                                                                                                                                      |

dbfiddle here


If you have very many _type, or you want your query to work with any possible _type (and not two specifically chosen) your second choice would be to have the following index:

CREATE INDEX idx_subscriber_type ON selected_media (subscriber_id, _type) ;

... then, the previous query will execute using this index

| QUERY PLAN                                                                                                                                         |
| :------------------------------------------------------------------------------------------------------------------------------------------------- |
| Result  (cost=1643.07..1643.09 rows=1 width=16) (actual time=0.679..0.680 rows=1 loops=1)                                                          |
|   InitPlan 1 (returns $0)                                                                                                                          |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.670..0.671 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media  (cost=29.98..819.19 rows=933 width=0) (actual time=0.170..0.573 rows=992 loops=1)                |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                                  |
|                 Heap Blocks: exact=547                                                                                                             |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.113..0.113 rows=992 loops=1)      |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                              |
|   InitPlan 2 (returns $1)                                                                                                                          |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media selected_media_1  (cost=29.98..819.19 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                                 |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1)        |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                             |
| Planning time: 0.077 ms                                                                                                                            |
| Execution time: 0.706 ms                                                                                                                           |

dbfiddle here

3
  • This seems to be a great refactoring of my query, but I still need to do point #1 above (sorry I didn't explain it clearly). If the answer is 0 (or null in your case) then it needs to show the total count of from the media table (regardless of type) (select count(*) from media). So say theres 0 books but 12 movies, it should show 500 | 12 (if theres 500 total media records)
    – Tallboy
    Jun 24 '17 at 17:44
  • 1
    Sorry, I didn't get it. In that case, change this slightly: use case when ... = 0 then (select count(*) from media) else ... end
    – joanolo
    Jun 24 '17 at 17:56
  • Thanks so much, this is an incredible answer. I <3 this stack exchange.
    – Tallboy
    Jun 24 '17 at 18:02

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.