explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nr2R : Optimization for: plan #vo3iC

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.431 15.134 ↑ 102.0 1 1

HashAggregate (cost=343.63..344.65 rows=102 width=14) (actual time=15.132..15.134 rows=1 loops=1)

  • Group Key: a.system_code_value_desc
2. 7.600 11.703 ↑ 1.0 4,529 1

Hash Join (cost=54.43..332.21 rows=4,568 width=14) (actual time=0.442..11.703 rows=4,529 loops=1)

  • Hash Cond: (b.language_id = (a.system_code_value)::text)
3. 3.676 3.676 ↑ 1.0 5,120 1

Seq Scan on provider b (cost=0.00..242.20 rows=5,120 width=2) (actual time=0.008..3.676 rows=5,120 loops=1)

4. 0.003 0.427 ↑ 8.0 1 1

Hash (cost=54.33..54.33 rows=8 width=50) (actual time=0.427..0.427 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
5. 0.172 0.424 ↑ 8.0 1 1

Hash Join (cost=9.83..54.33 rows=8 width=50) (actual time=0.420..0.424 rows=1 loops=1)

  • Hash Cond: ((a.system_code_value)::text = (unnest(string_to_array($0, ','::text))))
6. 0.177 0.221 ↓ 2.2 225 1

Bitmap Heap Scan on system_code_detail a (cost=5.76..49.80 rows=102 width=18) (actual time=0.049..0.221 rows=225 loops=1)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
  • Heap Blocks: exact=7
7. 0.044 0.044 ↓ 2.2 225 1

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

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '04'::text) AND (eff_end_ts > now()))
8. 0.004 0.031 ↑ 100.0 1 1

Hash (cost=2.82..2.82 rows=100 width=32) (actual time=0.030..0.031 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.005 0.027 ↑ 100.0 1 1

HashAggregate (cost=1.82..2.82 rows=100 width=32) (actual time=0.026..0.027 rows=1 loops=1)

  • Group Key: unnest(string_to_array($0, ','::text))
10. 0.011 0.022 ↑ 100.0 1 1

ProjectSet (cost=0.05..0.57 rows=100 width=32) (actual time=0.019..0.022 rows=1 loops=1)

11.          

Initplan (forProjectSet)

12. 0.003 0.008 ↑ 1.0 1 1

Limit (cost=0.00..0.05 rows=1 width=2) (actual time=0.005..0.008 rows=1 loops=1)

13. 0.005 0.005 ↑ 5,120.0 1 1

Seq Scan on provider (cost=0.00..242.20 rows=5,120 width=2) (actual time=0.003..0.005 rows=1 loops=1)

14. 0.003 0.003 ↑ 1.0 1 1

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