explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xDqq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=64,410.98..64,421.20 rows=584 width=64) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=64,410.98..64,412.44 rows=584 width=64) (actual rows= loops=)

  • Sort Key: gt.name, og.id, og.name, tp.id, i.id, o.id
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=58,353.05..64,384.15 rows=584 width=64) (actual rows= loops=)

  • Hash Cond: ((o.id = u.data_source_id) AND (i.id = u.indicator_id) AND (tp.id = u.report_period_id))
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=38.76..3,619.97 rows=14,336 width=72) (actual rows= loops=)

  • Hash Cond: (id.domain_id = d.id)
  • Join Filter: ((id.domain_id = 3) OR (d.parent_domain_id = 3))
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=36.52..3,091.38 rows=198,912 width=76) (actual rows= loops=)

  • Hash Cond: (id.indicator_id = i.id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.60..2,529.29 rows=198,912 width=72) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.60..34.34 rows=448 width=64) (actual rows= loops=)

  • Join Filter: (tp.start_date <= s.data_aggregation_cutoff_date)
8. 0.000 0.000 ↓ 0.0

Seq Scan on time_period tp (cost=0.00..1.64 rows=64 width=12) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=1.60..12.60 rows=21 width=64) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.60..12.49 rows=21 width=64) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on setting s (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.60..11.27 rows=21 width=60) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.46..7.42 rows=21 width=56) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.18..5.26 rows=2 width=52) (actual rows= loops=)

  • Hash Cond: (og.group_type_id = gt.id)
  • Join Filter: ((((og.system_group IS TRUE) AND (gt.name <> 'Division Area'::text)) OR (gt.name = 'User Defined Organization Group'::text)) AND (((gt.name || ':'::text) || (og.id)::text) = ANY ('{Division:128,Division:144,All:1,"Local Health Area (LHA):60"}'::text[])))
15. 0.000 0.000 ↓ 0.0

Seq Scan on organization_group og (cost=0.00..3.67 rows=156 width=25) (actual rows= loops=)

  • Filter: (private IS FALSE)
16. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=6 width=36) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on group_type gt (cost=0.00..1.10 rows=6 width=36) (actual rows= loops=)

  • Filter: ((name <> 'Division Area'::text) OR (name = 'User Defined Organization Group'::text))
18. 0.000 0.000 ↓ 0.0

Index Scan using idx_clinic_group_member_clinic_group_id on organization_group_member ogm (cost=0.28..0.95 rows=13 width=8) (actual rows= loops=)

  • Index Cond: (organization_group_id = og.id)
  • Filter: ((deleted IS NOT TRUE) AND (member_status_id = 'A'::bpchar))
19. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_organization on organization o (cost=0.14..0.18 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = ogm.organization_id)
20. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..9.66 rows=444 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on indicator_domain id (cost=0.00..7.44 rows=444 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=29.41..29.41 rows=441 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on indicator i (cost=0.00..29.41 rows=441 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=1.55..1.55 rows=55 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on domain d (cost=0.00..1.55 rows=55 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=49,143.22..49,143.22 rows=409,718 width=12) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on aggregate_data u (cost=13,183.74..49,143.22 rows=409,718 width=12) (actual rows= loops=)

  • Recheck Cond: (data_source_type = 'Organization'::text)
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mat_aggregate_data_data_source_type_data_source_id_report_p_idx (cost=0.00..13,081.32 rows=409,718 width=0) (actual rows= loops=)

  • Index Cond: (data_source_type = 'Organization'::text)