explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2SUF

Settings
# exclusive inclusive rows x rows loops node
1. 5.861 546,872.252 ↑ 7,423,498.9 7 1

Gather (cost=7,152,384.58..86,153,279.87 rows=51,964,492 width=8) (actual time=544,861.032..546,872.252 rows=7 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=106,324,480 read=7,474,369
  • I/O Timings: read=692,938.126
2. 2.231 546,866.391 ↑ 10,825,936.0 2 3 / 3

Nested Loop (cost=7,151,384.58..80,955,830.67 rows=21,651,872 width=8) (actual time=543,994.327..546,866.391 rows=2 loops=3)

  • Buffers: shared hit=106,324,480 read=7,474,369
  • I/O Timings: read=692,938.126
3. 3,404.783 546,815.140 ↑ 8,833.9 2,451 3 / 3

Merge Semi Join (cost=7,151,384.57..26,609,631.94 rows=21,651,872 width=469) (actual time=311,192.779..546,815.140 rows=2,451 loops=3)

  • Merge Cond: (a.transaction_id = "ANY_subquery".transaction_id)
  • Buffers: shared hit=106,324,480 read=7,474,369
  • I/O Timings: read=692,938.126
4. 445,333.687 445,333.687 ↑ 1.2 34,681,856 3 / 3

Parallel Index Scan using pk_transaction on transaction a (cost=0.57..19,349,903.90 rows=43,303,743 width=477) (actual time=0.728..445,333.687 rows=34,681,856 loops=3)

  • Buffers: shared hit=82,974,789 read=7,334,009
  • I/O Timings: read=601,270.649
5. 6.911 98,076.670 ↑ 1.3 7,353 3 / 3

Sort (cost=7,151,384.00..7,151,407.10 rows=9,238 width=8) (actual time=98,074.779..98,076.670 rows=7,353 loops=3)

  • Sort Key: "ANY_subquery".transaction_id
  • Sort Method: quicksort Memory: 537kB
  • Worker 0: Sort Method: quicksort Memory: 537kB
  • Worker 1: Sort Method: quicksort Memory: 537kB
  • Buffers: shared hit=23,349,691 read=140,360
  • I/O Timings: read=91,667.477
6. 2.497 98,069.759 ↑ 1.3 7,353 3 / 3

Subquery Scan on ANY_subquery (cost=1.00..7,150,775.52 rows=9,238 width=8) (actual time=35,191.371..98,069.759 rows=7,353 loops=3)

  • Buffers: shared hit=23,349,683 read=140,360
  • I/O Timings: read=91,667.477
7. 10.024 98,067.262 ↑ 1.3 7,353 3 / 3

GroupAggregate (cost=1.00..7,150,683.14 rows=9,238 width=16) (actual time=35,191.371..98,067.262 rows=7,353 loops=3)

  • Group Key: t1.account_id
  • Buffers: shared hit=23,349,683 read=140,360
  • I/O Timings: read=91,667.477
8. 5.699 98,057.238 ↑ 1.2 7,716 3 / 3

Nested Loop (cost=1.00..7,150,544.57 rows=9,238 width=16) (actual time=15,434.787..98,057.238 rows=7,716 loops=3)

  • Buffers: shared hit=23,349,683 read=140,360
  • I/O Timings: read=91,667.477
9. 89,064.951 89,064.951 ↑ 1.0 7,354 3 / 3

Index Scan using account_id on sale s1 (cost=0.43..7,029,248.19 rows=7,557 width=8) (actual time=15,433.673..89,064.951 rows=7,354 loops=3)

  • Filter: ((portfolio_id)::text = 'BCDLEP20200903'::text)
  • Rows Removed by Filter: 8,185,533
  • Buffers: shared hit=23,249,943 read=128,513
  • I/O Timings: read=82,758.923
10. 8,986.588 8,986.588 ↑ 1.0 1 22,062 / 3

Index Scan using idx_transaction_account_id on transaction t1 (cost=0.57..16.04 rows=1 width=16) (actual time=1.187..1.222 rows=1 loops=22,062)

  • Index Cond: (account_id = s1.account_id)
  • Filter: ((transaction_status)::text = 'BAL_IMPACTING'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=99,740 read=11,847
  • I/O Timings: read=8,908.554
11. 49.020 49.020 ↓ 0.0 0 7,353 / 3

Function Scan on json_array_elements prnallocs (cost=0.01..2.51 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=7,353)

  • Filter: (((value ->> 'bucketName'::text) = 'PRINCIPAL'::text) AND (((value ->> 'allocationAmount'::text))::numeric <= '25'::numeric))
  • Rows Removed by Filter: 8
Planning time : 8.180 ms