Question 250558 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to do counts in batch for graphql data loader?

I’m implementing a Graphql resolver for complex datatype. I try to use a data loader to avoid N+1 problem. I have a datatype with a counter. Therefore, in one graphql query I need to perform user counting multiple times for defferent types. Currently, I’m trying to combine multiple COUNT(*) queries within one SQL query. See the pseudocode below.

Pseudo code

# My type
type MyType {
  users_count: number
}

# The query returns an array of "MyType" 
getMyType(id: ID!): [MyType!]!

# Graphql query
getMyType(id: ID!) {
    id
    users_count
    created_at
}

# This is how I'm trying to do multiple counts
# 
SELECT 
    (SELECT COUNT(*) FROM table_with_users WHERE my_type_id = 1),
    (SELECT COUNT(*) FROM table_with_users WHERE my_type_id = 2)

The problem is I may have dozens of counts in one query. Is this going to be efficient for DB? What is the most efficient way of counting in this case?

Tags:

2

Answers


  1. 0

    A far more efficient way to do those multiple counts is to do it using a single group by query.

    SELECT
  my_type_id
, COUNT(*) as count_of
FROM table_with_users 
WHERE my_type_id IN(1,2,3) -- adjust this detail as needed
GROUP BY
  my_type_id

    This will produce 1 row per my_type_id.

    If you absolutely must have just one row with many columns, then you can still achieve that with a single query:

    SELECT
       COUNT(CASE WHEN my_type_id = 1 THEN 1 END) AS type_1
     , COUNT(CASE WHEN my_type_id = 2 THEN 1 END) AS type_2
     , COUNT(CASE WHEN my_type_id = 3 THEN 1 END) AS type_3
FROM table_with_users
WHERE my_type_id IN (1,2,3)
    Login or Signup to reply.
  2. 0

    If you have a table_with_users index on my_type_id, doing multiple subselects should be just fine.

    If you need to do a full table scan anyway (or have more complicated, possibly overlapping, conditions than just matching on my_type_id), it might be more efficient to use multiple count expressions in parallel, filtering for different rows:

    SELECT
  COUNT(*) FILTER (WHERE my_type_id = 1) AS count_of_type_1,
  COUNT(*) FILTER (WHERE my_type_id = 2) AS count_of_type_2
FROM table_with_users

    But if in doubt, do a benchmark yourself, and also try @PaulMaxwell’s GROUP BY suggestion.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search