explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4pIo

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 56.453 29,555.597 ↑ 1.0 5,120 1

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

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

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=18) (actual time=0.021..0.026 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
3. 0.015 0.015 ↑ 15.0 2 1

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

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

Hash (cost=242.20..242.20 rows=5,120 width=434) (actual time=13.037..13.038 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1414kB
5. 5.791 5.791 ↑ 1.0 5,120 1

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

6.          

SubPlan (forHash Right Join)

7. 20.480 1,894.400 ↑ 1.0 1 5,120

Result (cost=53.08..53.10 rows=1 width=32) (actual time=0.369..0.370 rows=1 loops=5,120)

8.          

Initplan (forResult)

9. 20.480 1,873.920 ↑ 8.0 1 5,120

Unique (cost=53.04..53.08 rows=8 width=14) (actual time=0.363..0.366 rows=1 loops=5,120)

10. 35.840 1,853.440 ↑ 8.0 1 5,120

Sort (cost=53.04..53.06 rows=8 width=14) (actual time=0.362..0.362 rows=1 loops=5,120)

  • Sort Key: system_code_detail_1.system_code_value_desc
  • Sort Method: quicksort Memory: 25kB
11. 824.320 1,817.600 ↑ 8.0 1 5,120

Hash Semi Join (cost=8.53..52.92 rows=8 width=14) (actual time=0.333..0.355 rows=1 loops=5,120)

  • Hash Cond: ((system_code_detail_1.system_code_value)::text = (unnest(string_to_array(pd.language_id, ','::text))))
12. 803.840 952.320 ↓ 2.2 225 5,120

Bitmap Heap Scan on system_code_detail system_code_detail_1 (cost=5.76..49.80 rows=102 width=18) (actual time=0.033..0.186 rows=225 loops=5,120)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
  • Heap Blocks: exact=35840
13. 148.480 148.480 ↓ 2.2 225 5,120

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..5.73 rows=102 width=0) (actual time=0.029..0.029 rows=225 loops=5,120)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
14. 10.240 40.960 ↑ 100.0 1 5,120

Hash (cost=1.52..1.52 rows=100 width=32) (actual time=0.008..0.008 rows=1 loops=5,120)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 25.600 30.720 ↑ 100.0 1 5,120

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.004..0.006 rows=1 loops=5,120)

16. 5.120 5.120 ↑ 1.0 1 5,120

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=5,120)

17. 30.720 27,591.680 ↑ 1.0 1 5,120

Result (cost=301.07..301.08 rows=1 width=32) (actual time=5.388..5.389 rows=1 loops=5,120)

18.          

Initplan (forResult)

19. 20.480 27,560.960 ↑ 1.0 2 5,120

Unique (cost=301.06..301.07 rows=2 width=14) (actual time=5.380..5.383 rows=2 loops=5,120)

20. 35.840 27,540.480 ↑ 1.0 2 5,120

Sort (cost=301.06..301.07 rows=2 width=14) (actual time=5.378..5.379 rows=2 loops=5,120)

  • Sort Key: system_code_detail_2.system_code_value_desc
  • Sort Method: quicksort Memory: 25kB
21. 113.960 27,504.640 ↑ 1.0 2 5,120

Hash Join (cost=267.51..301.05 rows=2 width=14) (actual time=5.353..5.372 rows=2 loops=5,120)

  • Hash Cond: ((system_code_detail_2.system_code_value)::text = (provider_speciality_xref.provider_speciality_id)::text)
22. 110.490 167.640 ↓ 1.9 34 3,810

Bitmap Heap Scan on system_code_detail system_code_detail_2 (cost=4.55..38.02 rows=18 width=18) (actual time=0.018..0.044 rows=34 loops=3,810)

  • Recheck Cond: (((system_code_id)::text = '300'::text) AND ((system_code_type)::text = '02'::text) AND (eff_end_ts > now()))
  • Heap Blocks: exact=19050
23. 57.150 57.150 ↓ 1.9 34 3,810

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.55 rows=18 width=0) (actual time=0.015..0.015 rows=34 loops=3,810)

  • Index Cond: (((system_code_id)::text = '300'::text) AND ((system_code_type)::text = '02'::text) AND (eff_end_ts > now()))
24. 20.480 27,223.040 ↑ 11.0 2 5,120

Hash (cost=262.69..262.69 rows=22 width=2) (actual time=5.317..5.317 rows=2 loops=5,120)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 30.720 27,202.560 ↑ 11.0 2 5,120

HashAggregate (cost=262.47..262.69 rows=22 width=2) (actual time=5.312..5.313 rows=2 loops=5,120)

  • Group Key: (provider_speciality_xref.provider_speciality_id)::text
26. 27,171.840 27,171.840 ↑ 23.0 2 5,120

Seq Scan on provider_speciality_xref (cost=0.00..262.36 rows=46 width=2) (actual time=3.477..5.307 rows=2 loops=5,120)

  • Filter: (((provider_id)::text ~~* pd.provider_id) AND (eff_end_ts > now()))
  • Rows Removed by Filter: 9104