title: Selecting COUNT values for multiple joined tables separately
body: <p>We have an online course platform, where the data model is oriented around chapter, chapter_views and discussions.</p>
  <h1>Desired result:</h1>
  <p>I am trying to select these stats data and produce single table with the most popular/viewed chapters. The result should look similar to this:</p>
  <pre><code>chapter title | views count | discussions count |
  =================================================
  Chapter 01    | 258         | 12                |
  -------------------------------------------------
  Chapter 02    | 1254        | 985               |
  -------------------------------------------------
  Chapter 03    | 11          | 1                 |
  -------------------------------------------------
  </code></pre>
  <p>I can get views count easilly and fast by counting joined <code>ahoy_events</code> as <code>views</code> table. The problem is that I need to get discussions count per chapter as well. When I am trying to add <code>discussions_count</code> to the query as 'nested' select, it just takes too long to process.</p>
  <h1>Existing data structure</h1>
  <h2>Chapters</h2>
  <pre><code>column      | type
  ===================
  id          | uuid
  section_id  | uuid
  title       | character varying
  created_at  | timestamp
  updated_at  | timestamp
  </code></pre>
  <h2>Sections</h2>
  <pre><code>column      | type
  ===================
  id          | uuid
  course_id   | uuid
  title       | character varying
  created_at  | timestamp
  updated_at  | timestamp
  </code></pre>
  <h2>Courses</h2>
  <pre><code>column      | type
  ===================
  id          | uuid
  teacher_id  | uuid
  title       | character varying
  created_at  | timestamp
  updated_at  | timestamp
  </code></pre>
  <h2>Discussions</h2>
  <pre><code>column      | type
  ===================
  id          | uuid
  chapter_id  | uuid
  title       | character varying
  body        | text
  created_at  | timestamp
  updated_at  | timestamp
  </code></pre>
  <h2>Ahoy::Events - upon every chapter visit one row is created</h2>
  <pre><code>column      | type
  ===================
  id          | uuid
  user_id     | uuid
  name        | character varying (defaults to &quot;student.chapter.show&quot;
  properties  | jsonb (for chapter views: {chapter_id: '2sdfl1h123-12hh12'}
  created_at  | timestamp
  updated_at  | timestamp
  </code></pre>
  <p>Since its an Rails app, we used Ahoy gem to track multiple user events and actions. This is just one of them. The structure of <code>ahoy_events</code> table is therefore set in stone and we use the gem's rules to save data - that's why we use jsonb column to save data for this particular event type (chapter view).</p>
  <h1>My take on this problem</h1>
  <p>I have come up with this query:</p>
  <pre><code>SELECT
    chapters.id,
    chapters.title,
    (
      SELECT COUNT(views.id) AS views_count
      FROM chapters ch1
      LEFT JOIN ahoy_events views ON (views.properties-&gt;&gt;'chapter_id' = ch1.id::text)
      WHERE ch1.id = chapters.id
        AND views.time &gt;= '2021-05-01 00:00:00 +0200'
        AND views.time &lt;= '2021-05-31 00:00:00 +0200'
    ), (
      SELECT COUNT(discussions.id) AS discussions_count
      FROM chapters ch2
      LEFT JOIN discussions ON (discussions.chapter_id = ch2.id)
      WHERE ch2.id = chapters.id
        AND discussions.created_at &gt;= '2021-05-01 00:00:00 +0200'
        AND discussions.created_at &lt;= '2021-05-31 00:00:00 +0200'
    )
  FROM chapters
  LEFT JOIN sections ON (sections.id = chapters.section_id)
  WHERE sections.course_id = '7015d82d-863a-47b2-88a2-0120b13472be'
  GROUP BY 1
  ORDER BY views_count DESC
  LIMIT 10
  </code></pre>
  <p>which works, but it takes 90 seconds to process (ahoy_events table consists of about 10M rows).</p>
  <p>Can you guys help me to find out less complex and more efficient way to do this. Or do I have to split this into two separate queries?</p>