explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ydmb

Settings
# exclusive inclusive rows x rows loops node
1. 0.072 2.698 ↑ 1.8 36 1

HashAggregate (cost=654.64..655.29 rows=65 width=77) (actual time=2.671..2.698 rows=36 loops=1)

  • Group Key: a.provider_id, a.preference_desc
2. 0.038 2.626 ↑ 48.6 40 1

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

  • Hash Cond: (((pd.provider_id)::character varying(40))::text = (a.provider_id)::text)
3. 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)
4. 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
5. 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
6. 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.015..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
7. 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.011..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()))
8. 0.035 1.841 ↑ 1.9 40 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
9. 0.062 1.806 ↑ 1.9 40 1

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

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

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

  • Hash Cond: (a.rule_id = g.rule_id)
11. 0.248 0.535 ↑ 1.6 40 1

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

  • Join Filter: ((e.system_code_value_desc)::text = (a.preference_action)::text)
  • Rows Removed by Join Filter: 200
12. 0.071 0.087 ↑ 1.6 40 1

Bitmap Heap Scan on provider_preferences a (cost=4.80..242.93 rows=65 width=192) (actual time=0.030..0.087 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
13. 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.015..0.016 rows=54 loops=1)

  • Index Cond: ((provider_id)::text = '3'::text)
14. 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)

15. 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.015..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
16. 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.011..0.011 rows=6 loops=1)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '16'::text))
17. 0.216 0.493 ↑ 1.0 300 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
18. 0.277 0.277 ↑ 1.0 300 1

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

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 16
19. 0.284 0.653 ↑ 1.0 396 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
20. 0.369 0.369 ↑ 1.0 396 1

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

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