explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dlxX

Settings
# exclusive inclusive rows x rows loops node
1. 14.302 13,068.107 ↑ 1,274,733.3 3 1

GroupAggregate (cost=548,046.42..23,261,607.34 rows=3,824,200 width=164) (actual time=13,037.402..13,068.107 rows=3 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. 2.195 6.234 ↓ 18.6 1,861 1

Subquery Scan on ar (cost=0.00..8.55 rows=100 width=100) (actual time=0.020..6.234 rows=1,861 loops=1)

4. 3.231 4.039 ↓ 18.6 1,861 1

Result (cost=0.00..5.05 rows=100 width=32) (actual time=0.015..4.039 rows=1,861 loops=1)

5. 0.806 0.808 ↓ 18.6 1,861 1

ProjectSet (cost=0.00..1.55 rows=100 width=32) (actual time=0.007..0.808 rows=1,861 loops=1)

6. 0.002 0.002 ↑ 1.0 1 1

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

7. 23.676 13,010.782 ↑ 374.6 10,208 1

Sort (cost=548,037.87..557,598.37 rows=3,824,200 width=140) (actual time=13,006.703..13,010.782 rows=10,208 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: 1501kB
8. 74.840 12,987.106 ↑ 374.6 10,208 1

ProjectSet (cost=219.49..129,924.19 rows=3,824,200 width=140) (actual time=23.833..12,987.106 rows=10,208 loops=1)

9. 32.398 12,912.266 ↑ 3.7 10,208 1

Nested Loop Left Join (cost=219.49..71,605.14 rows=38,242 width=214) (actual time=23.821..12,912.266 rows=10,208 loops=1)

  • Join Filter: (lp.cid = cupg.cid)
  • Rows Removed by Join Filter: 2828
10. 31.438 68.828 ↑ 3.7 10,208 1

Hash Right Join (cost=219.24..3,438.53 rows=38,242 width=218) (actual time=21.797..68.828 rows=10,208 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: 50980
11. 16.194 16.194 ↑ 1.0 5,099 1

Seq Scan on property_groups pg (cost=0.00..242.75 rows=5,099 width=8) (actual time=0.588..16.194 rows=5,099 loops=1)

  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = 1826))
  • Rows Removed by Filter: 1
12. 0.026 21.196 ↑ 1.2 12 1

Hash (cost=218.75..218.75 rows=15 width=226) (actual time=21.196..21.196 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.029 21.170 ↑ 1.2 12 1

Nested Loop Left Join (cost=14.63..218.75 rows=15 width=226) (actual time=4.932..21.170 rows=12 loops=1)

  • Join Filter: (rs.cid = cupg.cid)
14. 0.009 16.998 ↑ 1.3 3 1

Nested Loop Left Join (cost=14.34..214.36 rows=4 width=218) (actual time=4.928..16.998 rows=3 loops=1)

15. 0.012 15.708 ↑ 1.3 3 1

Nested Loop Left Join (cost=14.05..212.75 rows=4 width=187) (actual time=4.924..15.708 rows=3 loops=1)

  • Filter: ((cu.is_disabled = 0) OR (rsr.reference_id = 0))
16. 0.008 5.397 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.57..180.38 rows=3 width=87) (actual time=4.895..5.397 rows=3 loops=1)

  • Join Filter: (rsr.report_schedule_recipient_type_id = 1)
17. 0.006 4.102 ↑ 1.0 3 1

Nested Loop (cost=0.29..8.55 rows=3 width=83) (actual time=4.091..4.102 rows=3 loops=1)

18. 1.070 1.070 ↑ 1.0 1 1

Index Only Scan using pk_report_schedules on report_schedules rs (cost=0.14..4.18 rows=1 width=8) (actual time=1.067..1.070 rows=1 loops=1)

  • Index Cond: ((cid = 1826) AND (id = 43))
  • Heap Fetches: 1
19. 3.026 3.026 ↑ 1.0 3 1

Index Scan using idx_report_schedule_recipients_report_schedule_id on report_schedule_recipients rsr (cost=0.14..4.28 rows=3 width=83) (actual time=3.021..3.026 rows=3 loops=1)

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

Index Scan using idx_company_user_groups_company_group_id on company_user_groups cug (cost=0.28..44.31 rows=399 width=12) (actual time=0.429..0.429 rows=0 loops=3)

  • Index Cond: (rsr.reference_id = company_group_id)
  • Filter: ((cid = 1826) AND (rsr.cid = cid))
21. 0.917 10.299 ↓ 0.0 0 3

Hash Right Join (cost=13.48..19.74 rows=1 width=116) (actual time=3.431..3.433 rows=0 loops=3)

  • Hash Cond: (pui.company_user_id = cu.id)
22. 8.260 8.260 ↓ 18.6 1,861 1

CTE Scan on psi_user_information pui (cost=0.00..6.00 rows=100 width=100) (actual time=0.023..8.260 rows=1,861 loops=1)

23. 0.009 1.122 ↓ 0.0 0 3

Hash (cost=13.45..13.45 rows=1 width=20) (actual time=0.374..0.374 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.570 1.113 ↓ 0.0 0 3

Bitmap Heap Scan on company_users cu (cost=9.49..13.45 rows=1 width=20) (actual time=0.371..0.371 rows=0 loops=3)

  • Recheck Cond: ((rsr.reference_id = id) OR ((cid = 1826) AND (cug.company_user_id = id)))
  • Filter: ((deleted_on IS NULL) AND (cid = 1826) 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.009 0.543 ↓ 0.0 0 3

BitmapOr (cost=9.49..9.49 rows=2 width=0) (actual time=0.180..0.181 rows=0 loops=3)

26. 0.531 0.531 ↓ 0.0 0 3

Bitmap Index Scan on idx_company_users_id (cost=0.00..2.29 rows=1 width=0) (actual time=0.177..0.177 rows=0 loops=3)

  • Index Cond: (rsr.reference_id = id)
27. 0.003 0.003 ↓ 0.0 0 3

Bitmap Index Scan on pk_company_users (cost=0.00..0.31 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3)

  • Index Cond: ((cid = 1826) AND (cug.company_user_id = id))
28. 1.281 1.281 ↓ 0.0 0 3

Index Scan using idx_company_employees_id on company_employees ce (cost=0.29..0.37 rows=1 width=47) (actual time=0.427..0.427 rows=0 loops=3)

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 1826) AND (cid = cu.cid))
29. 4.143 4.143 ↑ 3.3 3 3

Index Scan using idx_company_user_property_groups_company_user_id on company_user_property_groups cupg (cost=0.29..0.77 rows=10 width=12) (actual time=0.462..1.381 rows=3 loops=3)

  • Index Cond: (cu.id = company_user_id)
  • Filter: (cid = 1826)
30. 12,811.040 12,811.040 ↓ 0.0 0 10,208

Function Scan on load_properties lp (cost=0.25..2.00 rows=1 width=8) (actual time=1.255..1.255 rows=0 loops=10,208)

  • Filter: ((cid = 1826) AND (property_id = pg.id))
  • Rows Removed by Filter: 1
31.          

SubPlan (forGroupAggregate)

32. 13.503 43.023 ↓ 34.0 3,403 3

Group (cost=4.87..5.87 rows=100 width=4) (actual time=6.895..14.341 rows=3,403 loops=3)

  • Group Key: (unnest(array_cat(array_agg(lp.property_id), array_agg(pg.id))))
33. 20.079 29.520 ↓ 68.0 6,805 3

Sort (cost=4.87..5.12 rows=100 width=4) (actual time=6.891..9.840 rows=6,805 loops=3)

  • Sort Key: (unnest(array_cat(array_agg(lp.property_id), array_agg(pg.id))))
  • Sort Method: quicksort Memory: 25kB
34. 9.438 9.441 ↓ 68.0 6,805 3

ProjectSet (cost=0.00..1.55 rows=100 width=4) (actual time=0.015..3.147 rows=6,805 loops=3)

35. 0.003 0.003 ↑ 1.0 1 3

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

Planning time : 5.672 ms