explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Lg7q : Optimization for: plan #0qCj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 40.444 2,111.193 ↑ 1.0 5,120 1

Hash Right Join (cost=310.91..416,917.65 rows=5,120 width=4,500) (actual time=12.984..2,111.193 rows=5,120 loops=1)

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

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=18) (actual time=0.025..0.030 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.019 0.019 ↑ 15.0 2 1

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

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

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

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

6.          

SubPlan (forHash Right Join)

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

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

8.          

Initplan (forResult)

9. 15.360 1,843.200 ↑ 8.0 1 5,120

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

10. 30.720 1,827.840 ↑ 8.0 1 5,120

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

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

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

  • Hash Cond: ((system_code_detail_1.system_code_value)::text = (unnest(string_to_array(pd.language_id, ','::text))))
12. 798.720 942.080 ↓ 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.031..0.184 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. 143.360 143.360 ↓ 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.028..0.028 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 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. 20.480 25.600 ↑ 100.0 1 5,120

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.003..0.005 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. 25.600 194.560 ↑ 1.0 1 5,120

Result (cost=28.15..28.16 rows=1 width=32) (actual time=0.037..0.038 rows=1 loops=5,120)

18.          

Initplan (forResult)

19. 20.480 168.960 ↓ 2.0 2 5,120

Unique (cost=28.14..28.15 rows=1 width=14) (actual time=0.030..0.033 rows=2 loops=5,120)

20. 25.600 148.480 ↓ 2.0 2 5,120

Sort (cost=28.14..28.15 rows=1 width=14) (actual time=0.028..0.029 rows=2 loops=5,120)

  • Sort Key: system_code_detail_2.system_code_value_desc
  • Sort Method: quicksort Memory: 25kB
21. 36.390 122.880 ↓ 2.0 2 5,120

Nested Loop (cost=4.58..28.13 rows=1 width=14) (actual time=0.013..0.024 rows=2 loops=5,120)

22. 15.360 40.960 ↑ 1.0 2 5,120

Bitmap Heap Scan on provider_speciality_xref (cost=4.30..11.50 rows=2 width=2) (actual time=0.007..0.008 rows=2 loops=5,120)

  • Recheck Cond: ((provider_id)::text = pd.provider_id)
  • Filter: (eff_end_ts > now())
  • Heap Blocks: exact=3873
23. 25.600 25.600 ↑ 1.0 2 5,120

Bitmap Index Scan on provider_speciality_xref_pkey (cost=0.00..4.30 rows=2 width=0) (actual time=0.005..0.005 rows=2 loops=5,120)

  • Index Cond: ((provider_id)::text = pd.provider_id)
24. 45.530 45.530 ↑ 1.0 1 9,106

Index Scan using system_code_detail_pkey on system_code_detail system_code_detail_2 (cost=0.28..8.31 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=9,106)

  • Index Cond: (((system_code_id)::text = '300'::text) AND ((system_code_type)::text = '02'::text) AND ((system_code_value)::text = (provider_speciality_xref.provider_speciality_id)::text) AND (eff_end_ts > now()))