explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z7zj

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

Hash Right Join (cost=310.91..1,814,265.16 rows=5,120 width=4,500) (actual rows= loops=)

  • Hash Cond: ((system_code_detail.system_code_value)::text = COALESCE((pd.gender)::text, 'U'::text))
2. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=18) (actual rows= loops=)

  • 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)
3. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (actual rows= loops=)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
4. 0.000 0.000 ↓ 0.0

Hash (cost=242.20..242.20 rows=5,120 width=434) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Seq Scan on provider pd (cost=0.00..242.20 rows=5,120 width=434) (actual rows= loops=)

6.          

SubPlan (forHash Right Join)

7. 0.000 0.000 ↓ 0.0

Result (cost=53.08..53.10 rows=1 width=32) (actual rows= loops=)

8.          

Initplan (forResult)

9. 0.000 0.000 ↓ 0.0

Unique (cost=53.04..53.08 rows=8 width=14) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=53.04..53.06 rows=8 width=14) (actual rows= loops=)

  • Sort Key: system_code_detail_1.system_code_value_desc
11. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=8.53..52.92 rows=8 width=14) (actual rows= loops=)

  • Hash Cond: ((system_code_detail_1.system_code_value)::text = (unnest(string_to_array(pd.language_id, ','::text))))
12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on system_code_detail system_code_detail_1 (cost=5.76..49.80 rows=102 width=18) (actual rows= loops=)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..5.73 rows=102 width=0) (actual rows= loops=)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
14. 0.000 0.000 ↓ 0.0

Hash (cost=1.52..1.52 rows=100 width=32) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Result (cost=301.07..301.08 rows=1 width=32) (actual rows= loops=)

18.          

Initplan (forResult)

19. 0.000 0.000 ↓ 0.0

Unique (cost=301.06..301.07 rows=2 width=14) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=301.06..301.07 rows=2 width=14) (actual rows= loops=)

  • Sort Key: system_code_detail_2.system_code_value_desc
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=267.51..301.05 rows=2 width=14) (actual rows= loops=)

  • Hash Cond: ((system_code_detail_2.system_code_value)::text = (provider_speciality_xref.provider_speciality_id)::text)
22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on system_code_detail system_code_detail_2 (cost=4.55..38.02 rows=18 width=18) (actual rows= loops=)

  • Recheck Cond: (((system_code_id)::text = '300'::text) AND ((system_code_type)::text = '02'::text) AND (eff_end_ts > now()))
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.55 rows=18 width=0) (actual rows= loops=)

  • Index Cond: (((system_code_id)::text = '300'::text) AND ((system_code_type)::text = '02'::text) AND (eff_end_ts > now()))
24. 0.000 0.000 ↓ 0.0

Hash (cost=262.69..262.69 rows=22 width=2) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

HashAggregate (cost=262.47..262.69 rows=22 width=2) (actual rows= loops=)

  • Group Key: (provider_speciality_xref.provider_speciality_id)::text
26. 0.000 0.000 ↓ 0.0

Seq Scan on provider_speciality_xref (cost=0.00..262.36 rows=46 width=2) (actual rows= loops=)

  • Filter: (((provider_id)::text ~~* pd.provider_id) AND (eff_end_ts > now()))