CODEWITHSUNDEEP
sqlpostgresql
NoDisplayName
NoDisplayName

Reputation: 15736

How to optimize multiple subqueries to the same data set

Imagine I have a query like the following one:

SELECT
  u.ID,
  ( SELECT 
      COUNT(*)
    FROM
      POSTS p
    WHERE
      p.USER_ID = u.ID
      AND p.TYPE = 1
  ) AS interesting_posts,
  ( SELECT 
      COUNT(*)
    FROM
      POSTS p
    WHERE
      p.USER_ID = u.ID
      AND p.TYPE = 2
  ) AS boring_posts,
  ( SELECT 
      COUNT(*)
    FROM
      COMMENTS c
    WHERE
      c.USER_ID = u.ID
      AND c.TYPE = 1
  ) AS interesting_comments,
  ( SELECT 
      COUNT(*)
    FROM
      COMMENTS c
    WHERE
      c.USER_ID = u.ID
      AND c.TYPE = 2
  ) AS boring_comments
FROM
  USERS u;

( Hopefully it's correct because I just came up with it and didn't test it )

where I try to calculate the number of interesting and boring posts and comments that the user has.

Now, the problem with this query is that we have 2 sequential scans on both the posts and comments table and I wonder if there is a way to avoid that?

I could probably LEFT JOIN both posts and comments to the users table and do some aggregation but it's gonna generate a lot of rows before aggregation and I am not sure if that's a good way to go.

Upvotes: 1

Views: 137

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Aggregate posts and comments and outer join them to the users table.

select
  u.id as user_id,
  coaleasce(p.interesting, 0) as interesting_posts,
  coaleasce(p.boring, 0)      as boring_posts,
  coaleasce(c.interesting, 0) as interesting_comments,
  coaleasce(c.boring, 0)      as boring_comments
from users u
left join
(
  select
    user_id,
    count(case when type = 1 then 1 end) as interesting,
    count(case when type = 2 then 1 end) as boring
  from posts
  group by user_id
) p on p.user_id = u.id
left join
(
  select
    user_id,
    count(case when type = 1 then 1 end) as interesting,
    count(case when type = 2 then 1 end) as boring
  from comments
  group by user_id
) c on c.user_id = u.id;

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51456

compare results and execution plan (here you scan posts once):

with c as (
select distinct 
count(1) filter (where TYPE = 1) over (partition by USER_ID) interesting_posts
, count(1) filter (where TYPE = 2) over (partition by USER_ID) boring_posts
, USER_ID
)
, p as (select USER_ID,max(interesting_posts) interesting_posts, max(boring_posts) boring_posts from c)
SELECT
  u.ID, interesting_posts,boring_posts
  ,  ( SELECT 
      COUNT(*)
    FROM
      COMMENTS c
    WHERE
      c.USER_ID = u.ID
  ) AS comments
FROM
  USERS u
JOIN p on p.USER_ID = u.ID

Upvotes: 0

Related Questions