explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P1cB

Settings
# exclusive inclusive rows x rows loops node
1. 14.058 1,871.549 ↑ 1.0 5,120 1

Hash Right Join (cost=310.91..272,255.02 rows=5,120 width=130) (actual time=9.587..1,871.549 rows=5,120 loops=1)

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

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.025..0.031 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.018..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. 4.479 9.140 ↑ 1.0 5,120 1

Hash (cost=242.20..242.20 rows=5,120 width=8) (actual time=9.139..9.140 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 270kB
5. 4.661 4.661 ↑ 1.0 5,120 1

Seq Scan on provider pd (cost=0.00..242.20 rows=5,120 width=8) (actual time=0.009..4.661 rows=5,120 loops=1)

6.          

SubPlan (forHash Right Join)

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

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

8.          

Initplan (forResult)

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

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

10. 25.600 1,812.480 ↑ 8.0 1 5,120

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

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

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

  • Hash Cond: ((system_code_detail_1.system_code_value)::text = (unnest(string_to_array(pd.language_id, ','::text))))
12. 793.600 936.960 ↓ 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.030..0.183 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. 10.240 35.840 ↑ 100.0 1 5,120

Hash (cost=1.52..1.52 rows=100 width=32) (actual time=0.007..0.007 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)