explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JeT9 : Optimization for: Optimization for: plan #HrnB; plan #Co98

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.016 2,847.325 ↑ 1.0 25 1

Limit (cost=475,718.92..475,718.98 rows=25 width=82) (actual time=2,847.303..2,847.325 rows=25 loops=1)

2.          

CTE srep

3. 90.315 90.315 ↑ 1.1 877 1

Function Scan on rep_num_id_function (cost=0.25..10.25 rows=1,000 width=4) (actual time=90.232..90.315 rows=877 loops=1)

4. 12.813 2,847.309 ↑ 1,863.1 25 1

Sort (cost=475,708.67..475,825.11 rows=46,578 width=82) (actual time=2,847.301..2,847.309 rows=25 loops=1)

  • Sort Key: ca.account_number
  • Sort Method: top-N heapsort Memory: 29kB
5. 3.365 2,834.496 ↑ 2.0 23,428 1

Subquery Scan on ca (cost=460,212.79..474,394.27 rows=46,578 width=82) (actual time=2,825.064..2,834.496 rows=23,428 loops=1)

6. 12.679 2,831.131 ↑ 2.0 23,428 1

WindowAgg (cost=460,212.79..473,928.49 rows=46,578 width=81) (actual time=2,825.063..2,831.131 rows=23,428 loops=1)

7. 5.291 2,818.452 ↑ 2.0 23,428 1

Hash Join (cost=460,212.79..473,229.82 rows=46,578 width=35) (actual time=2,813.848..2,818.452 rows=23,428 loops=1)

  • Hash Cond: (srep.rep_num_id = acc.rep_number_id)
8. 90.506 90.506 ↑ 1.1 877 1

CTE Scan on srep (cost=0.00..20.00 rows=1,000 width=4) (actual time=90.234..90.506 rows=877 loops=1)

9. 164.330 2,722.655 ↓ 1.1 540,320 1

Hash (cost=453,948.59..453,948.59 rows=501,136 width=35) (actual time=2,722.655..2,722.655 rows=540,320 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 39359kB
10. 776.442 2,558.325 ↓ 1.1 540,320 1

Hash Left Join (cost=286.21..453,948.59 rows=501,136 width=35) (actual time=1.030..2,558.325 rows=540,320 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: 1588329
11. 197.424 1,781.836 ↓ 1.0 2,128,649 1

Append (cost=283.32..448,250.74 rows=2,124,805 width=56) (actual time=0.943..1,781.836 rows=2,128,649 loops=1)

12. 5.541 6.401 ↓ 1.0 12,794 1

Bitmap Heap Scan on account_0 acc (cost=283.32..1,891.93 rows=12,778 width=66) (actual time=0.942..6.401 rows=12,794 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=717
13. 0.860 0.860 ↓ 1.0 12,794 1

Bitmap Index Scan on indx_account_0_view_status (cost=0.00..280.12 rows=12,778 width=0) (actual time=0.860..0.860 rows=12,794 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
14. 119.783 137.668 ↑ 1.0 294,177 1

Bitmap Heap Scan on account_1_10000 acc_1 (cost=6,487.59..59,729.13 rows=294,602 width=66) (actual time=19.846..137.668 rows=294,177 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=13745
15. 17.885 17.885 ↑ 1.0 294,177 1

Bitmap Index Scan on indx_account_1_10000_view_status (cost=0.00..6,413.94 rows=294,602 width=0) (actual time=17.885..17.885 rows=294,177 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
16. 88.716 103.811 ↓ 1.0 230,620 1

Bitmap Heap Scan on account_10000_20000 acc_2 (cost=5,027.48..48,886.57 rows=227,490 width=66) (actual time=16.661..103.811 rows=230,620 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=10975
17. 15.095 15.095 ↓ 1.0 230,620 1

Bitmap Index Scan on indx_account_10000_20000_view_status (cost=0.00..4,970.61 rows=227,491 width=0) (actual time=15.095..15.095 rows=230,620 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
18. 98.031 120.141 ↓ 1.0 222,917 1

Bitmap Heap Scan on account_20000_30000 acc_3 (cost=4,833.02..52,526.94 rows=217,367 width=66) (actual time=24.140..120.141 rows=222,917 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=10951
19. 22.110 22.110 ↓ 1.0 222,917 1

Bitmap Index Scan on indx_account_20000_30000_view_status (cost=0.00..4,778.68 rows=217,367 width=0) (actual time=22.110..22.110 rows=222,917 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
20. 72.739 85.222 ↑ 1.0 186,603 1

Bitmap Heap Scan on account_30000_40000 acc_4 (cost=4,229.70..52,731.34 rows=188,551 width=66) (actual time=13.817..85.222 rows=186,603 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=8924
21. 12.483 12.483 ↑ 1.0 186,603 1

Bitmap Index Scan on indx_account_30000_40000_view_status (cost=0.00..4,182.56 rows=188,551 width=0) (actual time=12.483..12.483 rows=186,603 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
22. 64.618 75.454 ↑ 1.0 161,104 1

Bitmap Heap Scan on account_40000_50000 acc_5 (cost=3,673.53..49,807.21 rows=163,239 width=67) (actual time=11.962..75.454 rows=161,104 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=7726
23. 10.836 10.836 ↑ 1.0 161,104 1

Bitmap Index Scan on indx_account_40000_50000_view_status (cost=0.00..3,632.72 rows=163,239 width=0) (actual time=10.836..10.836 rows=161,104 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
24. 108.482 125.873 ↑ 1.0 271,033 1

Bitmap Heap Scan on account_50000_60000 acc_6 (cost=6,132.08..76,978.38 rows=273,503 width=66) (actual time=19.198..125.873 rows=271,033 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=12576
25. 17.391 17.391 ↑ 1.0 271,033 1

Bitmap Index Scan on indx_account_50000_60000_view_status (cost=0.00..6,063.70 rows=273,503 width=0) (actual time=17.391..17.391 rows=271,033 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
26. 75.963 88.651 ↓ 1.0 194,141 1

Bitmap Heap Scan on account_60000_70000 acc_7 (cost=4,306.38..44,564.90 rows=193,801 width=66) (actual time=14.023..88.651 rows=194,141 loops=1)

  • Recheck Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
  • Heap Blocks: exact=9143
27. 12.688 12.688 ↓ 1.0 194,141 1

Bitmap Index Scan on indx_account_60000_70000_view_status (cost=0.00..4,257.93 rows=193,801 width=0) (actual time=12.688..12.688 rows=194,141 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
28. 0.013 0.013 ↓ 0.0 0 1

Index Scan using indx_account_70000_80000_view_status on account_70000_80000 acc_8 (cost=0.14..8.16 rows=1 width=112) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (upper((view_status)::text) = 'ACTIVE'::text)
  • Filter: (client_id > 0)
29. 841.178 841.178 ↓ 1.0 555,260 1

Seq Scan on account_80000 acc_9 (cost=0.00..61,126.17 rows=553,473 width=27) (actual time=0.019..841.178 rows=555,260 loops=1)

  • Filter: ((client_id > 0) AND (upper((view_status)::text) = 'ACTIVE'::text))
  • Rows Removed by Filter: 641963
30. 0.019 0.047 ↑ 1.0 84 1

Hash (cost=1.84..1.84 rows=84 width=16) (actual time=0.047..0.047 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
31. 0.028 0.028 ↑ 1.0 84 1

Seq Scan on account_class_code_map accm (cost=0.00..1.84 rows=84 width=16) (actual time=0.017..0.028 rows=84 loops=1)