explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HrnB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 5,297.200 ↑ 1.0 25 1

Limit (cost=338,164.42..338,164.49 rows=25 width=176) (actual time=5,297.195..5,297.200 rows=25 loops=1)

2.          

CTE srep

3. 491.003 491.003 ↓ 86.0 86,027 1

Function Scan on rep_num_id_function (cost=0.25..10.25 rows=1,000 width=4) (actual time=485.958..491.003 rows=86,027 loops=1)

4.          

CTE cte_filtered_account

5. 267.371 3,219.983 ↓ 30.8 686,950 1

Nested Loop (cost=23.06..337,625.82 rows=22,294 width=635) (actual time=522.089..3,219.983 rows=686,950 loops=1)

6. 39.792 543.856 ↓ 430.1 86,027 1

HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=521.661..543.856 rows=86,027 loops=1)

  • Group Key: r.rep_num_id
7. 504.064 504.064 ↓ 86.0 86,027 1

CTE Scan on srep r (cost=0.00..20.00 rows=1,000 width=4) (actual time=485.959..504.064 rows=86,027 loops=1)

8. 2,408.756 2,408.756 ↓ 2.0 8 86,027

Index Scan using idx_account_cond_rep_number_id on account acc_1 (cost=0.56..1,687.97 rows=4 width=635) (actual time=0.004..0.028 rows=8 loops=86,027)

  • Index Cond: (rep_number_id = r.rep_num_id)
  • Filter: (upper((view_status)::text) = 'ACTIVE'::text)
  • Rows Removed by Filter: 4
9. 52.384 5,297.190 ↑ 13.3 25 1

Sort (cost=528.36..529.19 rows=333 width=176) (actual time=5,297.189..5,297.190 rows=25 loops=1)

  • Sort Key: ca.account_number
  • Sort Method: top-N heapsort Memory: 30kB
10. 16.184 5,244.806 ↓ 420.1 139,909 1

Subquery Scan on ca (cost=3.89..518.96 rows=333 width=176) (actual time=5,199.048..5,244.806 rows=139,909 loops=1)

11. 96.099 5,228.622 ↓ 420.1 139,909 1

WindowAgg (cost=3.89..515.63 rows=333 width=128) (actual time=5,199.047..5,228.622 rows=139,909 loops=1)

12. 532.804 5,132.523 ↓ 420.1 139,909 1

Hash Left Join (cost=3.89..510.64 rows=333 width=82) (actual time=527.695..5,132.523 rows=139,909 loops=1)

  • Hash Cond: (acc.account_class_code_map_id = accm.account_class_code_map_id)
  • Filter: (((acc.advisory_type_cd)::text ~~ '000%'::text) OR ((acc.account_number)::text ~~ '000%'::text) OR (upper((accm.account_class_description)::text) ~~ '000%'::text))
  • Rows Removed by Filter: 547041
13. 4,599.657 4,599.657 ↓ 30.8 686,950 1

CTE Scan on cte_filtered_account acc (cost=0.00..445.88 rows=22,294 width=108) (actual time=522.097..4,599.657 rows=686,950 loops=1)

14. 0.015 0.062 ↓ 1.0 88 1

Hash (cost=2.84..2.84 rows=84 width=16) (actual time=0.062..0.062 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
15. 0.047 0.047 ↓ 1.0 88 1

Seq Scan on account_class_code_map accm (cost=0.00..2.84 rows=84 width=16) (actual time=0.024..0.047 rows=88 loops=1)