explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CftU : Optimization for: Test_Query; plan #8IRJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 91.502 526,323.613 ↑ 1.0 1 1

Aggregate (cost=3,803,986.34..3,803,986.35 rows=1 width=0) (actual time=526,323.612..526,323.613 rows=1 loops=1)

2. 1,601.319 526,232.111 ↓ 195,332.8 1,953,328 1

Sort (cost=3,803,986.19..3,803,986.22 rows=10 width=94) (actual time=526,093.773..526,232.111 rows=1,953,328 loops=1)

  • Sort Key: cp.custodial_account_id, cp.custodial_security_id
  • Sort Method: quicksort Memory: 322180kB
3.          

CTE latest_valuations

4. 10,708.652 106,879.554 ↓ 112.6 1,098,423 1

HashAggregate (cost=3,562,893.93..3,562,991.48 rows=9,755 width=12) (actual time=105,948.666..106,879.554 rows=1,098,423 loops=1)

  • Group Key: cp_1.custodial_account_id
5. 92,684.864 96,170.902 ↓ 1.1 16,835,837 1

Bitmap Heap Scan on custodial_positions cp_1 (cost=361,640.49..3,488,726.17 rows=14,833,552 width=12) (actual time=3,681.747..96,170.902 rows=16,835,837 loops=1)

  • Recheck Cond: ((valued_on > (('now'::cstring)::date - '30 days'::interval)) AND (valued_on <= ('now'::cstring)::date))
  • Heap Blocks: exact=295495
6. 3,486.038 3,486.038 ↓ 1.4 21,368,127 1

Bitmap Index Scan on index_custodial_positions_on_valued_on (cost=0.00..357,932.10 rows=14,833,552 width=0) (actual time=3,486.038..3,486.038 rows=21,368,127 loops=1)

  • Index Cond: ((valued_on > (('now'::cstring)::date - '30 days'::interval)) AND (valued_on <= ('now'::cstring)::date))
7.          

CTE latest_jobs

8. 1,416.406 221,233.615 ↓ 51.0 1,098,423 1

HashAggregate (cost=118,517.30..118,732.58 rows=21,528 width=16) (actual time=221,011.804..221,233.615 rows=1,098,423 loops=1)

  • Group Key: cp_2.custodial_account_id
9. 976.663 219,817.209 ↓ 92.8 1,997,404 1

Nested Loop (cost=0.57..118,409.66 rows=21,528 width=16) (actual time=0.034..219,817.209 rows=1,997,404 loops=1)

10. 254.369 254.369 ↓ 112.6 1,098,423 1

CTE Scan on latest_valuations lav_1 (cost=0.00..195.10 rows=9,755 width=12) (actual time=0.000..254.369 rows=1,098,423 loops=1)

11. 218,586.177 218,586.177 ↑ 1.0 2 1,098,423

Index Scan using index_custodial_positions_on_valued_on_custodial_account_id on custodial_positions cp_2 (cost=0.57..12.10 rows=2 width=20) (actual time=0.185..0.199 rows=2 loops=1,098,423)

  • Index Cond: ((custodial_account_id = lav_1.custodial_account_id) AND (valued_on = lav_1.current_account_date))
12. 1,048.675 524,630.792 ↓ 195,332.8 1,953,328 1

Merge Join (cost=121,938.95..122,261.97 rows=10 width=94) (actual time=522,827.491..524,630.792 rows=1,953,328 loops=1)

  • Merge Cond: ((cp.custodial_account_id = lav.custodial_account_id) AND (cp.custodial_job_id = lav.latest_job))
13. 2,539.510 300,621.879 ↓ 92.8 1,997,404 1

Sort (cost=119,959.02..120,012.84 rows=21,528 width=102) (actual time=300,163.063..300,621.879 rows=1,997,404 loops=1)

  • Sort Key: cp.custodial_account_id, cp.custodial_job_id
  • Sort Method: quicksort Memory: 330037kB
14. 787.423 298,082.369 ↓ 92.8 1,997,404 1

Nested Loop (cost=0.57..118,409.66 rows=21,528 width=102) (actual time=105,948.705..298,082.369 rows=1,997,404 loops=1)

15. 107,267.767 107,267.767 ↓ 112.6 1,098,423 1

CTE Scan on latest_valuations lj (cost=0.00..195.10 rows=9,755 width=12) (actual time=105,948.670..107,267.767 rows=1,098,423 loops=1)

16. 190,027.179 190,027.179 ↑ 1.0 2 1,098,423

Index Scan using index_custodial_positions_on_valued_on_custodial_account_id on custodial_positions cp (cost=0.57..12.10 rows=2 width=94) (actual time=0.162..0.173 rows=2 loops=1,098,423)

  • Index Cond: ((custodial_account_id = lj.custodial_account_id) AND (valued_on = lj.current_account_date))
17. 1,377.123 222,960.238 ↓ 90.7 1,953,311 1

Sort (cost=1,979.92..2,033.74 rows=21,528 width=16) (actual time=222,664.413..222,960.238 rows=1,953,311 loops=1)

  • Sort Key: lav.custodial_account_id, lav.latest_job
  • Sort Method: quicksort Memory: 100641kB
18. 221,583.115 221,583.115 ↓ 51.0 1,098,423 1

CTE Scan on latest_jobs lav (cost=0.00..430.56 rows=21,528 width=16) (actual time=221,011.806..221,583.115 rows=1,098,423 loops=1)

Planning time : 0.935 ms
Execution time : 526,470.768 ms