explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DkQr

Settings
# exclusive inclusive rows x rows loops node
1. 13.741 245.224 ↑ 1,274,733.3 3 1

GroupAggregate (cost=479,906.75..23,193,467.67 rows=3,824,200 width=164) (actual time=216.512..245.224 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. 1.900 6.128 ↓ 18.6 1,861 1

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

4. 3.416 4.228 ↓ 18.6 1,861 1

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

5. 0.810 0.812 ↓ 18.6 1,861 1

ProjectSet (cost=0.00..1.55 rows=100 width=32) (actual time=0.006..0.812 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. 12.037 190.326 ↑ 374.6 10,208 1

Sort (cost=479,898.20..489,458.70 rows=3,824,200 width=140) (actual time=186.193..190.326 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. 22.199 178.289 ↑ 374.6 10,208 1

ProjectSet (cost=246.19..61,784.53 rows=3,824,200 width=140) (actual time=136.142..178.289 rows=10,208 loops=1)

9. 16.090 156.090 ↑ 3.7 10,208 1

Hash Right Join (cost=246.19..3,465.48 rows=38,242 width=214) (actual time=136.124..156.090 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
10. 13.804 13.804 ↑ 1.0 5,099 1

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

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

Hash (cost=245.70..245.70 rows=15 width=226) (actual time=126.196..126.196 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.044 126.168 ↑ 1.2 12 1

Nested Loop Left Join (cost=14.88..245.70 rows=15 width=226) (actual time=41.381..126.168 rows=12 loops=1)

13. 0.024 24.664 ↑ 1.2 12 1

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

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

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

15. 0.018 15.963 ↑ 1.3 3 1

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

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

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

  • Join Filter: (rsr.report_schedule_recipient_type_id = 1)
17. 0.008 4.138 ↑ 1.0 3 1

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

18. 1.149 1.149 ↑ 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.147..1.149 rows=1 loops=1)

  • Index Cond: ((cid = 1826) AND (id = 43))
  • Heap Fetches: 1
19. 2.981 2.981 ↑ 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=2.973..2.981 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.485 1.485 ↓ 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.495..0.495 rows=0 loops=3)

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

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

  • Hash Cond: (pui.company_user_id = cu.id)
  • Join Filter: (cu.id < 10000)
22. 8.082 8.082 ↓ 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.082 rows=1,861 loops=1)

23. 0.012 1.182 ↓ 0.0 0 3

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

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

Bitmap Heap Scan on company_users cu (cost=9.49..13.45 rows=1 width=20) (actual time=0.389..0.390 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.576 ↓ 0.0 0 3

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

26. 0.564 0.564 ↓ 0.0 0 3

Bitmap Index Scan on idx_company_users_id (cost=0.00..2.29 rows=1 width=0) (actual time=0.188..0.188 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. 2.505 2.505 ↓ 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.835..0.835 rows=0 loops=3)

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 1826) AND (cid = cu.cid))
29. 6.159 6.159 ↑ 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.406..2.053 rows=3 loops=3)

  • Index Cond: (cu.id = company_user_id)
  • Filter: (cid = 1826)
30. 101.460 101.460 ↑ 1.0 1 12

Function Scan on load_properties lp (cost=0.25..2.00 rows=1 width=8) (actual time=8.454..8.455 rows=1 loops=12)

  • Filter: ((cid = 1826) AND (cid = cupg.cid))
31.          

SubPlan (forGroupAggregate)

32. 13.317 41.157 ↓ 34.0 3,403 3

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

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

Sort (cost=4.87..5.12 rows=100 width=4) (actual time=6.467..9.280 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. 8.796 8.802 ↓ 68.0 6,805 3

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

35. 0.006 0.006 ↑ 1.0 1 3

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

Planning time : 6.100 ms