help with improve query



  • we have this query and it takes more than 12 minutes with some of our users is there any hint we can improve it.

    SELECT b.id,
            ARRAY_AGG(DISTINCT CONCAT(pci2.id, '~',
                                      pci2.status_id, '~',
                                      pci2.update_date, '~',
                                      pci2.insert_date, '~',
                                      pc2.unit_id ,'~',
                                      units.unit_type_id)) as project_checklists_instances_id,
            b.description,
            '2' as checklist_type,
            assign_to_all_project,
            b.last_update_date as update_date,
            CASE WHEN completed*100/count_items_times_units > 0 and completed*100/count_items_times_units < 1
                   THEN 1
                 WHEN abs( round(completed*100/count_items_times_units) - (completed*100/count_items_times_units) ) = 0.5
                   THEN ceil(completed*100/count_items_times_units)
                 ELSE round(completed*100/count_items_times_units)
            END as percentage,
            unit_type_name,
            unit_type_ids,
            unit_ids,
            array_length(unit_type_ids, 1) as unit_type_number,
            CASE WHEN failed > 0 THEN 3                                               -- Open
                 WHEN count_items_times_units = completed and not_started = 0 THEN 6  -- Completed
                 WHEN failed = 0 and completed > 0 THEN 2                             -- In Progress
                 ELSE 1                                                               -- Not Started
            END AS status_id,
            ( SELECT user_name FROM view_project_user_name
              WHERE project_id = :projectId   -- FIXME: See note above about this fragment; also update_date and insert_dates may be incorrectly mixed --
                    AND user_id = (select (CASE WHEN MAX(pci3.update_date) IS NOT NULL AND MAX(pci3.update_date) > MAX(pc3.update_date)
                                                THEN array_remove(array_agg( pci3.user_id order by pci3.insert_date desc ), NULL)
                                              ELSE array_remove(array_agg( pc3.user_id order by pc3.insert_date desc ), null) END)[1]
                                   from project_checklists pc3
                                        left join project_checklists_instances pci3
                                                    on pc3.id = pci3.project_checklist_id and not pci3.is_deleted
                                   where pc3.project_id = :projectId
                                         and pc3.checklist_id = b.id
                                         and not pc3.is_deleted )
              LIMIT 1 ) as last_updated_by
     from (
            SELECT id,
                   description,
                   assign_to_all_project,
                   max(update_date)                     as last_update_date,
                   sum(count_items_times_units)         as count_items_times_units,
                   array_agg(DISTINCT unit_type_name)   as unit_type_name,
                   array_agg(DISTINCT unit_type_ids)    as unit_type_ids,
                   array_agg(DISTINCT unit_id)          as unit_ids,
                   sum(count_not_started)               as not_started,
                   sum(count_completed)                 as completed,
                   sum(count_failed)                    as failed
            from (
                SELECT pc.checklist_id as id,
                       assign_to_all_project,
                       c.description,
                       GREATEST( MAX(pci.update_date), MAX(pc.update_date) )                               AS update_date,
                       GREATEST( COUNT(DISTINCT ir.item_id), 1 )                                            AS count_items,
                       CASE WHEN pci.id is null THEN COUNT(DISTINCT pc.id)*COUNT(DISTINCT i.id)
                            ELSE COUNT(DISTINCT pc.id)*COUNT(DISTINCT ir.item_id) END                      AS count_items_times_units,
                       CASE WHEN pci.id is null THEN COUNT(DISTINCT pc.id)*COUNT(DISTINCT i.id)
                            ELSE COUNT(DISTINCT CASE WHEN ir.status_id = 0 THEN ir.id ELSE NULL END) END    AS count_not_started,
                       COUNT( DISTINCT CASE WHEN ir.status_id = 2 THEN ir.id ELSE NULL END )                AS count_completed,
                       COUNT( DISTINCT CASE WHEN ir.status_id = 1 THEN ir.id ELSE NULL END )                AS count_failed,
                       COALESCE(project_unit_type.name, 'unassigned')                                       as unit_type_name,
                       project_unit_type.id                                                                 as unit_type_ids,
                       pc.unit_id
                FROM project_checklists pc
                     JOIN checklists c ON c.id = pc.checklist_id AND NOT c.is_deleted
                     LEFT JOIN project_checklists_instances pci
                               ON pci.project_checklist_id = pc.id
                                  AND pci.insert_date = (
                                    SELECT MAX(pci2.insert_date)
                                    FROM project_checklists_instances pci2
                                    WHERE pci.project_checklist_id = pci2.project_checklist_id
                                          and not pci2.is_deleted
                                  )
                                  AND NOT pci.is_deleted
                     LEFT JOIN items_reports ir ON ir.checklists_instances_id = pci.id AND NOT ir.is_deleted
                     JOIN items i ON i.checklist_id = c.id AND NOT i.is_deleted
                     LEFT JOIN unit_type_checklists utc on utc.project_id = pc.project_id
                                                           AND utc.checklist_id = pc.checklist_id
                                                           and not utc.is_deleted
                     JOIN units on units.id = pc.unit_id and not units.is_deleted
                     left join project_unit_type on project_unit_type.id = units.unit_type_id
                                                    and project_unit_type.project_id = pc.project_id
                WHERE NOT pc.is_deleted
                      AND c.type = 2
                      AND pc.project_id = :projectId
                      AND c.company_id = :companyId
                      AND (:checklistId::UUID IS NULL OR c.id = :checklistId)
                GROUP BY pc.checklist_id, pci.id, c.description, assign_to_all_project, project_unit_type.id,
                         pc.unit_id, project_unit_type
                order by update_date desc
              ) a
            group by id, description, assign_to_all_project
          ) b
          join project_checklists pc2 on pc2.checklist_id = b.id
                                         and pc2.project_id = :projectId
                                         and not pc2.is_deleted
          LEFT JOIN project_checklists_instances pci2 ON pci2.project_checklist_id = pc2.id
                                                         AND not pci2.is_deleted
          join units on units.id = pc2.unit_id
                        and not units.is_deleted
     group by b.id, b.description, assign_to_all_project, last_update_date, completed, count_items_times_units,
              unit_type_name, unit_type_ids, unit_ids, failed, not_started
     ORDER BY b.description
    

    I run EXPLAIN (ANALYZE, BUFFERS) before my query and here is the result

    GroupAggregate  (cost=48776.04..67239.10 rows=400 width=417) (actual time=374955.807..375018.143 rows=30 loops=1)
    "  Group Key: a.description, a.id, a.assign_to_all_project, (max(a.update_date)), (sum(a.count_completed)), (sum(a.count_items_times_units)), (array_agg(DISTINCT a.unit_type_name)), (array_agg(DISTINCT a.unit_type_ids)), (array_agg(DISTINCT a.unit_id)), (sum(a.count_failed)), (sum(a.count_not_started)), ((SubPlan 2)), (array_length((array_agg(DISTINCT a.unit_type_ids)), 1))"
    "  Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4859611 written=4863688"
      I/O Timings: read=0.377
      ->  Sort  (cost=48776.04..48777.77 rows=692 width=377) (actual time=374955.635..374991.972 rows=3071 loops=1)
    "        Sort Key: a.description, a.id, a.assign_to_all_project, (max(a.update_date)), (sum(a.count_completed)), (sum(a.count_items_times_units)), (array_agg(DISTINCT a.unit_type_name)), (array_agg(DISTINCT a.unit_type_ids)), (array_agg(DISTINCT a.unit_id)), (sum(a.count_failed)), (sum(a.count_not_started)), ((SubPlan 2)), (array_length((array_agg(DISTINCT a.unit_type_ids)), 1))"
            Sort Method: external merge  Disk: 17680kB
    "        Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4859611 written=4863688"
            I/O Timings: read=0.377
            ->  Nested Loop  (cost=9880.60..48743.40 rows=692 width=377) (actual time=372649.013..374899.612 rows=3071 loops=1)
    "              Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4857401 written=4861473"
                  I/O Timings: read=0.377
                  ->  Hash Right Join  (cost=9880.17..12081.51 rows=745 width=325) (actual time=372647.685..372692.136 rows=3071 loops=1)
                        Hash Cond: (pci2.project_checklist_id = pc2.id)
    "                    Buffers: shared hit=17319532 read=126 dirtied=1, temp read=4857401 written=4861473"
                        I/O Timings: read=0.377
                        ->  Seq Scan on project_checklists_instances pci2  (cost=0.00..2001.68 rows=52981 width=52) (actual time=0.011..12.283 rows=52975 loops=1)
                              Filter: (NOT is_deleted)
                              Rows Removed by Filter: 2994
                              Buffers: shared hit=1316 read=126 dirtied=1
                              I/O Timings: read=0.377
                        ->  Hash  (cost=9870.86..9870.86 rows=745 width=305) (actual time=372642.438..372642.450 rows=3042 loops=1)
                              Buckets: 1024 (originally 1024)  Batches: 8 (originally 1)  Memory Usage: 4095kB
    "                          Buffers: shared hit=17318216, temp read=4854162 written=4860139"
                              ->  Nested Loop  (cost=3042.42..9870.86 rows=745 width=305) (actual time=372617.334..372624.194 rows=3042 loops=1)
    "                                Buffers: shared hit=17318216, temp read=4854162 written=4858234"
                                    ->  GroupAggregate  (cost=3042.00..3143.83 rows=574 width=273) (actual time=372617.302..372621.184 rows=30 loops=1)
    "                                      Group Key: a.id, a.description, a.assign_to_all_project"
    "                                      Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                          ->  Sort  (cost=3042.00..3049.17 rows=2868 width=145) (actual time=372616.765..372616.984 rows=3042 loops=1)
    "                                            Sort Key: a.id, a.description, a.assign_to_all_project"
                                                Sort Method: quicksort  Memory: 868kB
    "                                            Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                ->  Subquery Scan on a  (cost=2841.44..2877.29 rows=2868 width=145) (actual time=372614.924..372615.779 rows=3042 loops=1)
    "                                                  Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                      ->  Sort  (cost=2841.44..2848.61 rows=2868 width=266) (actual time=372614.921..372615.194 rows=3042 loops=1)
    "                                                        Sort Key: (GREATEST(max(pci.update_date), max(pc.update_date))) DESC"
                                                            Sort Method: quicksort  Memory: 1013kB
    "                                                        Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                            ->  GroupAggregate  (cost=2468.81..2676.74 rows=2868 width=266) (actual time=329651.160..372611.465 rows=3042 loops=1)
    "                                                              Group Key: pc.checklist_id, pci.id, c.description, c.assign_to_all_project, project_unit_type.id, pc.unit_id"
    "                                                              Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                                  ->  Sort  (cost=2468.81..2475.98 rows=2868 width=278) (actual time=329637.576..347518.000 rows=27790661 loops=1)
    "                                                                    Sort Key: pc.checklist_id, pci.id, c.description, c.assign_to_all_project, project_unit_type.id, pc.unit_id"
                                                                        Sort Method: external merge  Disk: 7623392kB
    "                                                                    Buffers: shared hit=17314788, temp read=4769820 written=4773550"
                                                                        ->  Nested Loop  (cost=11.32..2304.10 rows=2868 width=278) (actual time=0.125..13827.202 rows=27790661 loops=1)
                                                                              Join Filter: (c.id = i.checklist_id)
                                                                              Buffers: shared hit=17314788
                                                                              ->  Nested Loop Left Join  (cost=10.91..1699.04 rows=153 width=278) (actual time=0.112..1075.388 rows=893928 loops=1)
                                                                                    Buffers: shared hit=883722
                                                                                    ->  Nested Loop Left Join  (cost=6.37..489.49 rows=11 width=242) (actual time=0.107..420.784 rows=141935 loops=1)
                                                                                          Join Filter: (project_unit_type.project_id = pc.project_id)
                                                                                          Buffers: shared hit=601812
                                                                                          ->  Nested Loop Left Join  (cost=6.09..486.04 rows=11 width=153) (actual time=0.088..160.635 rows=141935 loops=1)
                                                                                                Buffers: shared hit=176007
                                                                                                ->  Nested Loop Left Join  (cost=5.81..477.82 rows=11 width=153) (actual time=0.076..41.911 rows=3042 loops=1)
                                                                                                      Buffers: shared hit=28201
                                                                                                      ->  Nested Loop  (cost=5.39..345.28 rows=11 width=129) (actual time=0.065..22.460 rows=3042 loops=1)
                                                                                                            Buffers: shared hit=15622
                                                                                                            ->  Nested Loop  (cost=4.97..267.63 rows=12 width=113) (actual time=0.052..5.836 rows=3042 loops=1)
                                                                                                                  Buffers: shared hit=3454
                                                                                                                  ->  Bitmap Heap Scan on checklists c  (cost=4.55..55.80 rows=17 width=41) (actual time=0.028..0.079 rows=33 loops=1)
                                                                                                                        Recheck Cond: (company_id = '9d29d3e5-7c92-e462-bf13-154357d0a12e'::uuid)
                                                                                                                        Filter: ((NOT is_deleted) AND (type = 2))
                                                                                                                        Rows Removed by Filter: 2
                                                                                                                        Heap Blocks: exact=9
                                                                                                                        Buffers: shared hit=11
                                                                                                                        ->  Bitmap Index Scan on checklists_company_id_index  (cost=0.00..4.54 rows=35 width=0) (actual time=0.019..0.019 rows=90 loops=1)
                                                                                                                              Index Cond: (company_id = '9d29d3e5-7c92-e462-bf13-154357d0a12e'::uuid)
                                                                                                                              Buffers: shared hit=2
                                                                                                                  ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc  (cost=0.42..12.45 rows=1 width=72) (actual time=0.011..0.140 rows=92 loops=33)
                                                                                                                        Index Cond: ((checklist_id = c.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                                                                                                        Filter: (NOT is_deleted)
                                                                                                                        Rows Removed by Filter: 9
                                                                                                                        Buffers: shared hit=3443
                                                                                                            ->  Index Scan using units_pkey on units units_1  (cost=0.42..6.47 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=3042)
                                                                                                                  Index Cond: (id = pc.unit_id)
                                                                                                                  Filter: (NOT is_deleted)
                                                                                                                  Buffers: shared hit=12168
                                                                                                      ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci  (cost=0.41..12.04 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=3042)
                                                                                                            Index Cond: (project_checklist_id = pc.id)
                                                                                                            Filter: ((NOT is_deleted) AND (insert_date = (SubPlan 3)))
                                                                                                            Rows Removed by Filter: 0
                                                                                                            Buffers: shared hit=12579
                                                                                                            SubPlan 3
                                                                                                              ->  Aggregate  (cost=8.43..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=667)
                                                                                                                    Buffers: shared hit=2750
                                                                                                                    ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci2_1  (cost=0.41..8.43 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=667)
                                                                                                                          Index Cond: (pci.project_checklist_id = project_checklist_id)
                                                                                                                          Filter: (NOT is_deleted)
                                                                                                                          Rows Removed by Filter: 0
                                                                                                                          Buffers: shared hit=2750
                                                                                                ->  Index Scan using unit_type_checklists_checklist_id_project_id_unit_type_id_uk on unit_type_checklists utc  (cost=0.28..0.74 rows=1 width=32) (actual time=0.005..0.031 rows=47 loops=3042)
                                                                                                      Index Cond: ((checklist_id = pc.checklist_id) AND (project_id = pc.project_id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                                                                                      Filter: (NOT is_deleted)
                                                                                                      Rows Removed by Filter: 0
                                                                                                      Buffers: shared hit=147806
                                                                                          ->  Index Scan using project_unit_type_id_pk on project_unit_type  (cost=0.28..0.30 rows=1 width=137) (actual time=0.001..0.001 rows=1 loops=141935)
                                                                                                Index Cond: (id = units_1.unit_type_id)
                                                                                                Filter: (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid)
                                                                                                Buffers: shared hit=425805
                                                                                    ->  Bitmap Heap Scan on items_reports ir  (cost=4.54..109.62 rows=34 width=52) (actual time=0.001..0.003 rows=6 loops=141935)
                                                                                          Recheck Cond: (checklists_instances_id = pci.id)
                                                                                          Filter: (NOT is_deleted)
                                                                                          Heap Blocks: exact=183730
                                                                                          Buffers: shared hit=281910
                                                                                          ->  Bitmap Index Scan on items_reports_checklists_instances_id_index  (cost=0.00..4.53 rows=34 width=0) (actual time=0.001..0.001 rows=6 loops=141935)
                                                                                                Index Cond: (checklists_instances_id = pci.id)
                                                                                                Buffers: shared hit=98180
                                                                              ->  Index Scan using items_checklist_id_index on items i  (cost=0.41..3.70 rows=20 width=32) (actual time=0.002..0.010 rows=31 loops=893928)
                                                                                    Index Cond: (checklist_id = pc.checklist_id)
                                                                                    Filter: (NOT is_deleted)
                                                                                    Rows Removed by Filter: 0
                                                                                    Buffers: shared hit=16431066
                                    ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc2  (cost=0.42..11.70 rows=1 width=48) (actual time=0.010..0.082 rows=101 loops=30)
                                          Index Cond: ((checklist_id = a.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                          Filter: (NOT is_deleted)
                                          Rows Removed by Filter: 9
                                          Buffers: shared hit=3428
                  ->  Index Scan using units_pkey on units  (cost=0.42..6.47 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=3071)
                        Index Cond: (id = pc2.unit_id)
                        Filter: (NOT is_deleted)
                        Buffers: shared hit=12284
                  SubPlan 2
                    ->  Limit  (cost=21.92..46.01 rows=1 width=32) (actual time=0.714..0.714 rows=1 loops=3071)
                          Buffers: shared hit=3564198
                          InitPlan 1 (returns $2)
                            ->  Aggregate  (cost=20.91..20.93 rows=1 width=16) (actual time=0.704..0.704 rows=1 loops=3071)
                                  Buffers: shared hit=3533508
                                  ->  Nested Loop Left Join  (cost=0.84..20.89 rows=1 width=64) (actual time=0.006..0.558 rows=272 loops=3071)
                                        Buffers: shared hit=3533508
                                        ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc3  (cost=0.42..12.45 rows=1 width=48) (actual time=0.004..0.126 rows=269 loops=3071)
                                              Index Cond: ((checklist_id = a.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                              Filter: (NOT is_deleted)
                                              Rows Removed by Filter: 2
                                              Buffers: shared hit=850202
                                        ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci3  (cost=0.41..8.43 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=826972)
                                              Index Cond: (pc3.id = project_checklist_id)
                                              Filter: (NOT is_deleted)
                                              Rows Removed by Filter: 0
                                              Buffers: shared hit=2683306
                          ->  Nested Loop Left Join  (cost=0.99..25.08 rows=1 width=32) (actual time=0.713..0.713 rows=1 loops=3071)
                                Join Filter: ((upc.user_id = u.id) AND (up.sub_company_id = upc.sub_company_id))
                                Buffers: shared hit=3564198
                                ->  Nested Loop  (cost=0.70..16.75 rows=1 width=57) (actual time=0.711..0.711 rows=1 loops=3071)
                                      Buffers: shared hit=3555000
                                      ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=3071)
                                            Index Cond: (id = $2)
                                            Buffers: shared hit=9213
                                      ->  Index Scan using users_projects_user_id_project_id_pk on users_projects up  (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3071)
                                            Index Cond: ((user_id = $2) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                            Buffers: shared hit=12279
                                ->  Index Scan using user_permisions_c_idx on users_permissions_companies upc  (cost=0.29..8.31 rows=1 width=61) (actual time=0.002..0.002 rows=1 loops=3066)
                                      Index Cond: (user_id = $2)
                                      Buffers: shared hit=9198
    Planning Time: 23.193 ms
    Execution Time: 375932.570 ms
    

    Postgres Version 11.13

    here is the sample of the result to give a better stand of what happing

    [{"id":"fe68b609-0afc-5337-8316-ff605440151f","project_checklists_instances_id":["~~~~00e148bf-7316-8847-c651-1860831a62dd~","~~~~074ff140-5969-1cdf-2549-28ee790b237a~"],"description":"test item","checklist_type":"2","assign_to_all_project":false,"update_date":"2022-05-02T13:21:39.885Z","percentage":"0","unit_type_name":["unassigned"],"unit_type_ids":[null],"unit_ids":[""f7dc1ba4-a888-069c-7552-e26d0f44a90d"],"unit_type_number":1,"status_id":1,"last_updated_by":"Admin: Mina"}]
    


  • Going through the plan most of the time is spent doing a big sort of 27 million rows (5 mins 33 secs). That is never going to be fast. A good starting point is to reduce the amount of rows that really need sorting, I would do that by checking which of those joins are responsible for the massive row growth. By doing that you'll also get a bunch of collateral improvements because you'd be doing everything a whole lot less.

    If we accept the ~3K row result from the join between project_checklists pc and checklists c, this next jumps up from 3K to 141K rows when you join to unit_type_checklists utc. Looking at your SQL, you are doing an outer join to this table but you are not using any of the columns from it, on top of that all of your aggregations are just doing count(distinct.. so there's no way it matters what's being joined here. Let's scrap that join.

    The next big jump is from 141K rows to 894K rows when you join to item_reports. This join is only so you can run aggregations against this table. It would make a lot of sense to do these aggregations safely separately rather than joining to a bunch of other tables so you have to dedupe (which is probably why you've got the distincts in your counts. You can replace the join of the table to a join to a subquery:

    left join (select  ir.checklists_instances_id
                      ,count(*) cnt_all
                      ,count(CASE WHEN ir.status_id = 0 THEN 1 ELSE NULL END) cnt_status_0
                      ,count(CASE WHEN ir.status_id = 1 THEN 1 ELSE NULL END) cnt_status_1
                      ,count(CASE WHEN ir.status_id = 2 THEN 1 ELSE NULL END) cnt_status_2
               from    items_reports ir
               where   NOT ir.is_deleted
               group by ir.checklists_instances_id
              ) ir
    ON        ir.checklists_instances_id = pci.id
    

    Then you can replace the count(distinct's that use ir in your script, e.g

    count(DISTINCT
         CASE
                   WHEN ir.status_id = 0 THEN ir.id
                   ELSE NULL
         END)
    

    Would become

    ir.cnt_status_0
    

    The next massive row jump is from 894K to 27,791K when you join to item. This looks to be exactly the same problem as before

    join      (select i.checklist_id
                     ,count(*) cnt_all
               from   items i
               where  i.is_deleted
               group by i.checklist_id
              ) i
    ON        i.checklist_id = c.id
    

    Once we've done this it becomes quite clear what was going on with those count(distincts and it's probably worth cleaning up the rest of them. This is the sort of thing that would make sum impossible unless you used subqueries.

    The final step is to fix up the group by to make sure we're either including the new already aggregated columns or to remove it entirely because we've successfully moved the aggregates to respective subqueries only.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2