explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UZVj

Settings
# exclusive inclusive rows x rows loops node
1. 167.640 675.617 ↑ 1.2 57,607 1

Hash Left Join (cost=10,398.66..21,213.44 rows=69,364 width=863) (actual time=157.789..675.617 rows=57,607 loops=1)

  • Hash Cond: ((a.facility_id)::text = (facility_d.facility_id)::text)
2. 86.350 507.300 ↑ 1.0 57,607 1

Hash Left Join (cost=10,369.89..18,304.36 rows=60,342 width=799) (actual time=157.096..507.300 rows=57,607 loops=1)

  • Hash Cond: (a.rule_id = g.rule_id)
3. 89.687 420.478 ↑ 1.0 57,607 1

Hash Left Join (cost=10,355.40..17,255.69 rows=60,342 width=761) (actual time=156.615..420.478 rows=57,607 loops=1)

  • Hash Cond: ((a.preference_action)::text = (e.system_code_value_desc)::text)
4. 80.623 330.758 ↑ 1.0 57,607 1

Hash Left Join (cost=10,341.43..17,015.24 rows=60,342 width=746) (actual time=156.574..330.758 rows=57,607 loops=1)

  • Hash Cond: ((a.preference_parameter)::text = (b.attribute_name)::text)
5. 89.311 249.850 ↑ 1.0 57,607 1

Hash Right Join (cost=10,330.65..16,846.06 rows=60,342 width=695) (actual time=156.278..249.850 rows=57,607 loops=1)

  • Hash Cond: (((pd.provider_id)::character varying(40))::text = (a.provider_id)::text)
6. 4.580 13.911 ↑ 1.0 5,120 1

Hash Right Join (cost=362.11..435.50 rows=5,120 width=36) (actual time=9.341..13.911 rows=5,120 loops=1)

  • Hash Cond: ((system_code_detail.system_code_value)::text = COALESCE((pd.gender)::text, 'U'::text))
7. 0.019 0.043 ↑ 12.0 2 1

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.031..0.043 rows=2 loops=1)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
  • Filter: (is_active = 'Y'::bpchar)
  • Heap Blocks: exact=2
8. 0.024 0.024 ↑ 15.0 2 1

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (actual time=0.023..0.024 rows=2 loops=1)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
9. 4.542 9.288 ↑ 1.0 5,120 1

Hash (cost=293.40..293.40 rows=5,120 width=38) (actual time=9.287..9.288 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 346kB
10. 4.746 4.746 ↑ 1.0 5,120 1

Seq Scan on provider pd (cost=0.00..293.40 rows=5,120 width=38) (actual time=0.011..4.746 rows=5,120 loops=1)

11. 84.413 146.628 ↓ 1.0 57,607 1

Hash (cost=4,411.74..4,411.74 rows=57,584 width=663) (actual time=146.628..146.628 rows=57,607 loops=1)

  • Buckets: 8192 Batches: 16 Memory Usage: 1905kB
12. 62.215 62.215 ↓ 1.0 57,607 1

Seq Scan on provider_preferences a (cost=0.00..4,411.74 rows=57,584 width=663) (actual time=0.009..62.215 rows=57,607 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 309
13. 0.142 0.285 ↑ 1.0 168 1

Hash (cost=8.68..8.68 rows=168 width=67) (actual time=0.284..0.285 rows=168 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
14. 0.143 0.143 ↑ 1.0 168 1

Seq Scan on appointment_attributes_d b (cost=0.00..8.68 rows=168 width=67) (actual time=0.008..0.143 rows=168 loops=1)

15. 0.007 0.033 ↓ 6.0 6 1

Hash (cost=13.96..13.96 rows=1 width=29) (actual time=0.033..0.033 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.014 0.026 ↓ 6.0 6 1

Bitmap Heap Scan on system_code_detail e (cost=4.31..13.96 rows=1 width=29) (actual time=0.017..0.026 rows=6 loops=1)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '16'::text))
  • Filter: (flag1 = 1)
  • Heap Blocks: exact=3
17. 0.012 0.012 ↓ 2.0 6 1

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.31 rows=3 width=0) (actual time=0.011..0.012 rows=6 loops=1)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '16'::text))
18. 0.235 0.472 ↑ 1.0 300 1

Hash (cost=10.74..10.74 rows=300 width=42) (actual time=0.471..0.472 rows=300 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
19. 0.237 0.237 ↑ 1.0 300 1

Seq Scan on rule_d g (cost=0.00..10.74 rows=300 width=42) (actual time=0.008..0.237 rows=300 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 16
20. 0.306 0.677 ↑ 1.0 396 1

Hash (cost=23.82..23.82 rows=396 width=29) (actual time=0.676..0.677 rows=396 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
21. 0.371 0.371 ↑ 1.0 396 1

Seq Scan on facility_d (cost=0.00..23.82 rows=396 width=29) (actual time=0.007..0.371 rows=396 loops=1)

  • Filter: ((now() >= eff_bgn_ts) AND (now() <= eff_end_ts))
  • Rows Removed by Filter: 45