explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rHUy

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 2.641 ↑ 48.6 40 1

Hash Right Join (cost=351.05..644.92 rows=1,943 width=77) (actual time=1.908..2.641 rows=40 loops=1)

  • Hash Cond: (((pd.provider_id)::character varying(40))::text = (a.provider_id)::text)
2. 0.009 0.747 ↑ 26.0 1 1

Hash Left Join (cost=46.63..316.06 rows=26 width=4) (actual time=0.047..0.747 rows=1 loops=1)

  • Hash Cond: (COALESCE((pd.gender)::text, 'U'::text) = (system_code_detail.system_code_value)::text)
3. 0.716 0.716 ↑ 26.0 1 1

Seq Scan on provider pd (cost=0.00..267.80 rows=26 width=6) (actual time=0.018..0.716 rows=1 loops=1)

  • Filter: (((provider_id)::character varying(40))::text = '3'::text)
  • Rows Removed by Filter: 5119
4. 0.004 0.022 ↑ 12.0 2 1

Hash (cost=46.33..46.33 rows=24 width=4) (actual time=0.021..0.022 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
5. 0.007 0.018 ↑ 12.0 2 1

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.014..0.018 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
6. 0.011 0.011 ↑ 15.0 2 1

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

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
7. 0.034 1.845 ↑ 1.9 40 1

Hash (cost=303.48..303.48 rows=75 width=77) (actual time=1.845..1.845 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
8. 0.063 1.811 ↑ 1.9 40 1

Hash Left Join (cost=52.37..303.48 rows=75 width=77) (actual time=1.213..1.811 rows=40 loops=1)

  • Hash Cond: ((a.facility_id)::text = (facility_d.facility_id)::text)
9. 0.063 1.072 ↑ 1.6 40 1

Hash Left Join (cost=23.60..273.47 rows=65 width=80) (actual time=0.531..1.072 rows=40 loops=1)

  • Hash Cond: (a.rule_id = g.rule_id)
10. 0.254 0.537 ↑ 1.6 40 1

Nested Loop Left Join (cost=9.11..257.87 rows=65 width=84) (actual time=0.052..0.537 rows=40 loops=1)

  • Join Filter: ((e.system_code_value_desc)::text = (a.preference_action)::text)
  • Rows Removed by Join Filter: 200
11. 0.067 0.083 ↑ 1.6 40 1

Bitmap Heap Scan on provider_preferences a (cost=4.80..242.93 rows=65 width=192) (actual time=0.027..0.083 rows=40 loops=1)

  • Recheck Cond: ((provider_id)::text = '3'::text)
  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=21
12. 0.016 0.016 ↑ 1.2 54 1

Bitmap Index Scan on provider_preferences_provider_id_idx (cost=0.00..4.79 rows=66 width=0) (actual time=0.016..0.016 rows=54 loops=1)

  • Index Cond: ((provider_id)::text = '3'::text)
13. 0.177 0.200 ↓ 6.0 6 40

Materialize (cost=4.31..13.96 rows=1 width=14) (actual time=0.001..0.005 rows=6 loops=40)

14. 0.012 0.023 ↓ 6.0 6 1

Bitmap Heap Scan on system_code_detail e (cost=4.31..13.96 rows=1 width=14) (actual time=0.014..0.023 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
15. 0.011 0.011 ↓ 2.0 6 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
17. 0.243 0.243 ↑ 1.0 300 1

Seq Scan on rule_d g (cost=0.00..10.74 rows=300 width=4) (actual time=0.005..0.243 rows=300 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 16
18. 0.301 0.676 ↑ 1.0 396 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
19. 0.375 0.375 ↑ 1.0 396 1

Seq Scan on facility_d (cost=0.00..23.82 rows=396 width=3) (actual time=0.005..0.375 rows=396 loops=1)

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