0

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"}]
9
  • 1
    Also we need to know what indexes are currently defined on those tables. For all we know currently they could even be views which would expand the scope of the problem massively. Have a look at the "how to all good questions" pages in the documentation here. Apr 28, 2022 at 20:58
  • 2
    What version are you on? What is work_mem set to?
    – jjanes
    Apr 29, 2022 at 1:14
  • 1
    How much data is the query returning and how much data does the query need to process in order to return this? Is the query running faster in some cases, and if so, when? What is the execution plan in those cases? Seeing how it differs might give you a clue how to fix it for the slow cases — either by restructuring the query, adding indexes, or in newest Postgres versions, adding statistics. Apr 29, 2022 at 4:03
  • 1
    Can't answer for sure, but your design has multiple level of aggregation, and aggregation over distinct values... I would recommend revisiting the design; from the lower (least aggregated data) use subquery to pull the data you want to sum, and the count of the distinct things; and build up to the values you want to the result to show. The inefficiencies of sum(distinct) are performance dogs.
    – Alocyte
    Apr 29, 2022 at 9:09
  • 1
    You could at least present a properly formatted query. May 4, 2022 at 3:42

2 Answers 2

2
+50

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.

1
  • you are genius man, Thanks a lot that's really help me
    – Mina Fawzy
    May 4, 2022 at 22:09
1

It's helpful to see the explain in depesz , https://explain.depesz.com/s/nbl0, and one can see the only orange in the left hand column, the per line cost, is the part that is expensive. The second column is the cumulative cost of that row and it's children , and thus it generally makes it harder to see the most expensive operation.

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.