explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cBp4

Settings
# exclusive inclusive rows x rows loops node
1. 2.049 99.755 ↑ 502,200.0 1 1

GroupAggregate (cost=54,455.97..2,509,926.76 rows=502,200 width=164) (actual time=99.755..99.755 rows=1 loops=1)

  • Group Key: (COALESCE(cu.id, 0)), (COALESCE(ce.name_first, (pui.name_first)::character varying, ((rsr.details ->> 'name'::text))::character varying)), (COALESCE(ce.name_last, (pui.name_last)::character varying, ''::character varying)), (COALESCE(ce.email_address, (pui.email_address)::character varying, ((rsr.details ->> 'email'::text))::character varying)), (jsonb_array_elements_text(COALESCE((rsr.details -> 'output_locale'::text), '[""en_US""]'::jsonb)))
2.          

CTE psi_user_information

3. 6.826 28.617 ↓ 62.5 6,251 1

Subquery Scan on ar (cost=0.00..3.52 rows=100 width=100) (actual time=0.017..28.617 rows=6,251 loops=1)

4. 20.051 21.791 ↓ 62.5 6,251 1

Result (cost=0.00..2.02 rows=100 width=32) (actual time=0.013..21.791 rows=6,251 loops=1)

5. 1.739 1.740 ↓ 62.5 6,251 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.004..1.740 rows=6,251 loops=1)

6. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

7. 1.662 95.542 ↑ 401.4 1,251 1

Sort (cost=54,452.46..55,707.96 rows=502,200 width=140) (actual time=95.405..95.542 rows=1,251 loops=1)

  • Sort Key: (COALESCE(cu.id, 0)), (COALESCE(ce.name_first, (pui.name_first)::character varying, ((rsr.details ->> 'name'::text))::character varying)), (COALESCE(ce.name_last, (pui.name_last)::character varying, ''::character varying)), (COALESCE(ce.email_address, (pui.email_address)::character varying, ((rsr.details ->> 'email'::text))::character varying)), (jsonb_array_elements_text(COALESCE((rsr.details -> 'output_locale'::text), '[""en_US""]'::jsonb)))
  • Sort Method: quicksort Memory: 224kB
8. 4.734 93.880 ↑ 401.4 1,251 1

ProjectSet (cost=66.43..6,899.38 rows=502,200 width=140) (actual time=86.180..93.880 rows=1,251 loops=1)

9. 0.481 89.146 ↑ 4.0 1,251 1

Nested Loop Left Join (cost=66.43..4,313.05 rows=5,022 width=187) (actual time=86.163..89.146 rows=1,251 loops=1)

10. 1.700 40.952 ↑ 5,022.0 1 1

Hash Right Join (cost=66.18..496.08 rows=5,022 width=191) (actual time=38.979..40.952 rows=1 loops=1)

  • Hash Cond: (pg.cid = rs.cid)
  • Join Filter: CASE WHEN (cu.is_administrator = 1) THEN true WHEN ((cu.id IS NULL) AND (rsr.report_schedule_recipient_type_id = 5)) THEN true ELSE (cupg.property_group_id = pg.id) END
  • Rows Removed by Join Filter: 2,510
11. 2.314 2.314 ↑ 1.0 2,511 1

Index Scan using pk_property_groups on property_groups pg (cost=0.29..235.58 rows=2,511 width=8) (actual time=0.054..2.314 rows=2,511 loops=1)

  • Index Cond: (cid = 235)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 9
12. 0.014 36.938 ↑ 4.0 1 1

Hash (cost=65.85..65.85 rows=4 width=195) (actual time=36.938..36.938 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.005 36.924 ↑ 4.0 1 1

Nested Loop Left Join (cost=9.54..65.85 rows=4 width=195) (actual time=36.913..36.924 rows=1 loops=1)

  • Join Filter: (rs.cid = cupg.cid)
14. 0.006 36.834 ↑ 2.0 1 1

Nested Loop Left Join (cost=9.25..64.65 rows=2 width=191) (actual time=36.826..36.834 rows=1 loops=1)

15. 0.004 36.793 ↑ 2.0 1 1

Nested Loop Left Join (cost=8.97..63.99 rows=2 width=162) (actual time=36.785..36.793 rows=1 loops=1)

  • Filter: ((cu.is_disabled = 0) OR (rsr.reference_id = 0))
16. 0.002 0.174 ↑ 2.0 1 1

Nested Loop Left Join (cost=0.86..51.33 rows=2 width=62) (actual time=0.169..0.174 rows=1 loops=1)

  • Join Filter: (rsr.report_schedule_recipient_type_id = 1)
17. 0.002 0.164 ↑ 2.0 1 1

Nested Loop (cost=0.57..5.16 rows=2 width=58) (actual time=0.159..0.164 rows=1 loops=1)

18. 0.094 0.094 ↑ 1.0 1 1

Index Only Scan using pk_report_schedules on report_schedules rs (cost=0.28..2.30 rows=1 width=8) (actual time=0.092..0.094 rows=1 loops=1)

  • Index Cond: ((cid = 235) AND (id = 1,576))
  • Heap Fetches: 1
19. 0.068 0.068 ↑ 2.0 1 1

Index Scan using idx_report_schedule_recipients_report_schedule_id on report_schedule_recipients rsr (cost=0.29..2.85 rows=2 width=58) (actual time=0.066..0.068 rows=1 loops=1)

  • Index Cond: (report_schedule_id = 1,576)
  • Filter: ((cid = 235) AND (report_schedule_recipient_type_id = ANY ('{1,2,5}'::integer[])))
20. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_company_user_groups_company_group_id on company_user_groups cug (cost=0.29..22.89 rows=15 width=12) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (rsr.reference_id = company_group_id)
  • Filter: ((cid = 235) AND (rsr.cid = cid))
21. 2.447 36.615 ↑ 1.0 1 1

Hash Right Join (cost=8.11..10.37 rows=1 width=116) (actual time=36.612..36.615 rows=1 loops=1)

  • Hash Cond: (pui.company_user_id = cu.id)
22. 34.136 34.136 ↓ 62.5 6,251 1

CTE Scan on psi_user_information pui (cost=0.00..2.00 rows=100 width=100) (actual time=0.018..34.136 rows=6,251 loops=1)

23. 0.004 0.032 ↑ 1.0 1 1

Hash (cost=8.10..8.10 rows=1 width=20) (actual time=0.031..0.032 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.012 0.028 ↑ 1.0 1 1

Bitmap Heap Scan on company_users cu (cost=6.05..8.10 rows=1 width=20) (actual time=0.027..0.028 rows=1 loops=1)

  • Recheck Cond: (((cid = 235) AND (rsr.reference_id = id)) OR ((cid = 235) AND (cug.company_user_id = id)))
  • Filter: ((deleted_on IS NULL) AND (company_user_type_id = 2) AND (rsr.cid = cid) AND (((rsr.report_schedule_recipient_type_id = 2) AND (rsr.reference_id = id)) OR (cug.company_user_id = id)))
  • Heap Blocks: exact=1
25. 0.002 0.016 ↓ 0.0 0 1

BitmapOr (cost=6.05..6.05 rows=2 width=0) (actual time=0.016..0.016 rows=0 loops=1)

26. 0.014 0.014 ↑ 1.0 1 1

Bitmap Index Scan on pk_company_users (cost=0.00..1.43 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((cid = 235) AND (rsr.reference_id = id))
27. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on pk_company_users (cost=0.00..0.52 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: ((cid = 235) AND (cug.company_user_id = id))
28. 0.035 0.035 ↑ 1.0 1 1

Index Scan using idx_company_employees_id on company_employees ce (cost=0.28..0.32 rows=1 width=45) (actual time=0.035..0.035 rows=1 loops=1)

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 235) AND (cid = cu.cid))
29. 0.085 0.085 ↑ 8.0 1 1

Index Only Scan using uk_cupg_cid_company_user_id_property_group_id on company_user_property_groups cupg (cost=0.29..0.50 rows=8 width=12) (actual time=0.083..0.085 rows=1 loops=1)

  • Index Cond: ((cid = 235) AND (company_user_id = cu.id))
  • Heap Fetches: 1
30. 47.713 47.713 ↓ 1,251.0 1,251 1

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=8) (actual time=47.179..47.713 rows=1,251 loops=1)

  • Filter: ((cid = 235) AND (cid = pg.cid))
31.          

SubPlan (for GroupAggregate)

32. 0.693 2.164 ↓ 12.5 1,252 1

Group (cost=3.84..4.84 rows=100 width=4) (actual time=1.194..2.164 rows=1,252 loops=1)

  • Group Key: (unnest(array_cat(array_agg(lp.property_id), array_agg(pg.id))))
33. 0.980 1.471 ↓ 25.0 2,502 1

Sort (cost=3.84..4.09 rows=100 width=4) (actual time=1.192..1.471 rows=2,502 loops=1)

  • Sort Key: (unnest(array_cat(array_agg(lp.property_id), array_agg(pg.id))))
  • Sort Method: quicksort Memory: 214kB
34. 0.490 0.491 ↓ 25.0 2,502 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.011..0.491 rows=2,502 loops=1)

35. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)