explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0qCj

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 57.940 29,556.528 ↑ 1.0 5,120 1

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

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

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

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (actual time=0.020..0.021 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. 6.724 12.475 ↑ 1.0 5,120 1

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

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

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

6.          

SubPlan (for Hash Right Join)

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

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

8.          

Initplan (for Result)

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

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

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

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

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

Hash Semi Join (cost=8.53..52.92 rows=8 width=14) (actual time=0.335..0.357 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 957.440 ↓ 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.187 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. 153.600 153.600 ↓ 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.030..0.030 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. 15.360 46.080 ↑ 100.0 1 5,120

Hash (cost=1.52..1.52 rows=100 width=32) (actual time=0.009..0.009 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,581.440 ↑ 1.0 1 5,120

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

18.          

Initplan (for Result)

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

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

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

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

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

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

  • Hash Cond: ((system_code_detail_2.system_code_value)::text = (provider_speciality_xref.provider_speciality_id)::text)
22. 106.680 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.019..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. 60.960 60.960 ↓ 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.016..0.016 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,207.680 ↑ 11.0 2 5,120

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

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

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

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

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

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