explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bQoS

Settings
# exclusive inclusive rows x rows loops node
1. 13.077 12,318.352 ↑ 1,274,733.3 3 1

GroupAggregate (cost=543,265.30..23,256,826.22 rows=3,824,200 width=164) (actual time=12,290.255..12,318.352 rows=3 loops=1)

  • Group Key: (COALESCE(cu.id, 0)), (COALESCE(ce.name_first, ((rsr.details ->> 'name'::text))::character varying)), (COALESCE(ce.name_last, ''::character varying)), (COALESCE(ce.email_address, ((rsr.details ->> 'email'::text))::character varying)), (jsonb_array_elements_text(COALESCE((rsr.details -> 'output_locale'::text), '[""en_US""]'::jsonb)))
2. 18.973 12,265.912 ↑ 374.6 10,208 1

Sort (cost=543,265.30..552,825.80 rows=3,824,200 width=140) (actual time=12,261.963..12,265.912 rows=10,208 loops=1)

  • Sort Key: (COALESCE(cu.id, 0)), (COALESCE(ce.name_first, ((rsr.details ->> 'name'::text))::character varying)), (COALESCE(ce.name_last, ''::character varying)), (COALESCE(ce.email_address, ((rsr.details ->> 'email'::text))::character varying)), (jsonb_array_elements_text(COALESCE((rsr.details -> 'output_locale'::text), '[""en_US""]'::jsonb)))
  • Sort Method: quicksort Memory: 1501kB
3. 70.859 12,246.939 ↑ 374.6 10,208 1

ProjectSet (cost=227.16..125,151.62 rows=3,824,200 width=140) (actual time=1.569..12,246.939 rows=10,208 loops=1)

4. 33.193 12,176.080 ↑ 3.7 10,208 1

Nested Loop Left Join (cost=227.16..66,832.57 rows=38,242 width=118) (actual time=1.560..12,176.080 rows=10,208 loops=1)

  • Join Filter: (lp.cid = cupg.cid)
  • Rows Removed by Join Filter: 15734
5. 29.581 36.199 ↑ 3.7 10,208 1

Hash Right Join (cost=226.91..3,446.20 rows=38,242 width=122) (actual time=0.159..36.199 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
6. 6.475 6.475 ↑ 1.0 5,099 1

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

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

Hash (cost=226.42..226.42 rows=15 width=130) (actual time=0.143..0.143 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
8. 0.015 0.130 ↑ 1.2 12 1

Nested Loop Left Join (cost=10.64..226.42 rows=15 width=130) (actual time=0.044..0.130 rows=12 loops=1)

  • Join Filter: (rs.cid = cupg.cid)
9. 0.005 0.085 ↑ 1.3 3 1

Nested Loop Left Join (cost=10.35..222.04 rows=4 width=122) (actual time=0.041..0.085 rows=3 loops=1)

  • Filter: ((cu.is_disabled = 0) OR (rsr.reference_id = 0))
10. 0.005 0.044 ↑ 1.0 3 1

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

  • Join Filter: (rsr.report_schedule_recipient_type_id = 1)
11. 0.005 0.027 ↑ 1.0 3 1

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

12. 0.011 0.011 ↑ 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=0.010..0.011 rows=1 loops=1)

  • Index Cond: ((cid = 1826) AND (id = 43))
  • Heap Fetches: 1
13. 0.011 0.011 ↑ 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=0.008..0.011 rows=3 loops=1)

  • Index Cond: (report_schedule_id = 43)
  • Filter: ((cid = 1826) AND (report_schedule_recipient_type_id = ANY ('{1,2,5}'::integer[])))
14. 0.012 0.012 ↓ 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.004..0.004 rows=0 loops=3)

  • Index Cond: (rsr.reference_id = company_group_id)
  • Filter: ((cid = 1826) AND (rsr.cid = cid))
15. 0.008 0.036 ↓ 0.0 0 3

Nested Loop Left Join (cost=9.78..13.85 rows=1 width=55) (actual time=0.011..0.012 rows=0 loops=3)

16. 0.006 0.021 ↓ 0.0 0 3

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

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

18. 0.006 0.006 ↓ 0.0 0 3

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

  • Index Cond: (rsr.reference_id = id)
19. 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.000..0.001 rows=0 loops=3)

  • Index Cond: ((cid = 1826) AND (cug.company_user_id = id))
20. 0.007 0.007 ↑ 1.0 1 1

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

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 1826) AND (cid = cu.cid))
21. 0.030 0.030 ↑ 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.003..0.010 rows=3 loops=3)

  • Index Cond: (cu.id = company_user_id)
  • Filter: (cid = 1826)
22. 12,106.688 12,106.688 ↓ 2.0 2 10,208

Function Scan on load_properties lp (cost=0.25..1.88 rows=1 width=8) (actual time=1.185..1.186 rows=2 loops=10,208)

  • Filter: (cid = 1826)
23.          

SubPlan (forGroupAggregate)

24. 12.690 39.363 ↓ 34.0 3,403 3

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

  • Group Key: (unnest(array_cat(array_agg(lp.property_id), array_agg(pg.id))))
25. 18.282 26.673 ↓ 68.0 6,805 3

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

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

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

27. 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 : 1.547 ms