explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qBeg : AJX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.649 8,048.866 ↓ 4.9 322 1

Nested Loop Left Join (cost=29,279.91..1,938,676.87 rows=66 width=310) (actual time=321.436..8,048.866 rows=322 loops=1)

  • Join Filter: (s.settlementtype_id = 8)
  • Buffers: shared hit=1,257,438
2. 2.568 8,037.421 ↓ 4.9 322 1

Nested Loop Left Join (cost=29,279.49..1,937,890.44 rows=66 width=241) (actual time=321.399..8,037.421 rows=322 loops=1)

  • Buffers: shared hit=1,256,069
3. 2.097 41.847 ↓ 4.9 322 1

Nested Loop Left Join (cost=0.57..5,479.47 rows=66 width=105) (actual time=7.246..41.847 rows=322 loops=1)

  • Buffers: shared hit=11,854
4. 37.174 37.174 ↓ 4.9 322 1

Index Scan Backward using "IX_settlement_counttime" on settlement s (cost=0.29..5,380.00 rows=66 width=114) (actual time=7.207..37.174 rows=322 loops=1)

  • Filter: ((parentsettlement_id IS NULL) AND (settlementtype_id = 8) AND (COALESCE(source_moneystorage_id, sink_moneystorage_id) = '4a5b7872-31c1-11e9-9fdd-0050568b70b8'::uuid))
  • Rows Removed by Filter: 66,403
  • Buffers: shared hit=10,888
5. 2.576 2.576 ↑ 1.0 1 322

Index Scan using pkey_user on "user" u (cost=0.28..1.51 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=322)

  • Index Cond: (user_id = s.user_id)
  • Buffers: shared hit=966
6. 8.372 7,993.006 ↑ 1.0 1 322

Aggregate (cost=29,278.92..29,278.93 rows=1 width=136) (actual time=24.822..24.823 rows=1 loops=322)

  • Buffers: shared hit=1,244,215
7. 5.324 7,984.634 ↑ 50.0 6 322

Hash Left Join (cost=6,934.42..29,274.41 rows=300 width=48) (actual time=14.270..24.797 rows=6 loops=322)

  • Hash Cond: (cs.settlement_id = es.parentsettlement_id)
  • Buffers: shared hit=1,244,215
8. 6.245 7,975.940 ↑ 50.0 6 322

Hash Left Join (cost=6,407.16..28,738.87 rows=300 width=28) (actual time=14.256..24.770 rows=6 loops=322)

  • Hash Cond: ((s_1.paymenttype_id = rs_1.paymenttype_id) AND (s_1.source_moneystorage_id = rs_1.source_moneystorage_id) AND (COALESCE(s_1.parentsettlement_id, s_1.settlement_id) = COALESCE(ps.parentsettlement_id, rs_1.parentsettlement_id)))
  • Buffers: shared hit=1,243,636
9. 11.270 7,702.562 ↑ 50.0 6 322

Nested Loop Left Join (cost=57.92..22,038.40 rows=300 width=88) (actual time=13.420..23.921 rows=6 loops=322)

  • Buffers: shared hit=1,238,635
10. 7,175.448 7,175.448 ↑ 50.0 6 322

Seq Scan on settlement cs (cost=0.00..4,654.88 rows=300 width=60) (actual time=12.076..22.284 rows=6 loops=322)

  • Filter: ((COALESCE(parentsettlement_id, settlement_id) = s.settlement_id) AND (settlementtype_id = 8))
  • Rows Removed by Filter: 66,719
  • Buffers: shared hit=1,176,588
11. 5.796 515.844 ↓ 0.0 0 1,932

Limit (cost=57.92..57.93 rows=1 width=717) (actual time=0.267..0.267 rows=0 loops=1,932)

  • Buffers: shared hit=62,047
12. 77.280 510.048 ↓ 0.0 0 1,932

Sort (cost=57.92..57.93 rows=1 width=717) (actual time=0.264..0.264 rows=0 loops=1,932)

  • Sort Key: s_1.counttime DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=62,047
13. 107.226 432.768 ↓ 27.0 27 1,932

Nested Loop (cost=2.69..57.91 rows=1 width=717) (actual time=0.066..0.224 rows=27 loops=1,932)

  • Buffers: shared hit=62,047
14. 34.776 79.212 ↓ 0.0 0 1,932

Nested Loop (cost=0.00..2.96 rows=1 width=16) (actual time=0.041..0.041 rows=0 loops=1,932)

  • Join Filter: (pt.mainpaymenttype_id = mpt.paymenttype_id)
  • Rows Removed by Join Filter: 12
  • Buffers: shared hit=3,864
15. 11.592 11.592 ↑ 1.0 1 1,932

Seq Scan on paymenttype pt (cost=0.00..1.32 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1,932)

  • Filter: (paymenttype_id = cs.paymenttype_id)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=1,932
16. 32.844 32.844 ↑ 1.2 13 1,932

Seq Scan on paymenttype mpt (cost=0.00..1.45 rows=15 width=4) (actual time=0.006..0.017 rows=13 loops=1,932)

  • Filter: isphysical
  • Rows Removed by Filter: 30
  • Buffers: shared hit=1,932
17. 212.520 246.330 ↓ 40.0 160 322

Bitmap Heap Scan on settlement s_1 (cost=2.69..54.91 rows=4 width=76) (actual time=0.137..0.765 rows=160 loops=322)

  • Recheck Cond: ((source_moneystorage_id = cs.source_moneystorage_id) AND (counttime < cs.counttime))
  • Filter: ((paymenttype_id = cs.paymenttype_id) AND (settlementtype_id = 8))
  • Rows Removed by Filter: 802
  • Heap Blocks: exact=55,762
  • Buffers: shared hit=58,183
18. 33.810 33.810 ↓ 35.7 963 322

Bitmap Index Scan on "IX_settlement_source_moneystorage_id_counttime" (cost=0.00..2.69 rows=27 width=0) (actual time=0.105..0.105 rows=963 loops=322)

  • Index Cond: ((source_moneystorage_id = cs.source_moneystorage_id) AND (counttime < cs.counttime))
  • Buffers: shared hit=2,421
19. 1.954 267.133 ↑ 2.2 1,385 1

Hash (cost=6,295.03..6,295.03 rows=3,098 width=68) (actual time=267.132..267.133 rows=1,385 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=5,001
20. 4.823 265.179 ↑ 2.2 1,385 1

Hash Join (cost=5,155.60..6,295.03 rows=3,098 width=68) (actual time=254.839..265.179 rows=1,385 loops=1)

  • Hash Cond: (rs_1.parentsettlement_id = ps.settlement_id)
  • Buffers: shared hit=5,001
21. 5.807 5.807 ↓ 1.0 3,147 1

Index Scan using settlement_settlementtype_id_idx on settlement rs_1 (cost=0.29..1,131.58 rows=3,098 width=52) (actual time=0.037..5.807 rows=3,147 loops=1)

  • Index Cond: (settlementtype_id = 2)
  • Buffers: shared hit=1,347
22. 121.498 254.549 ↑ 1.0 66,725 1

Hash (cost=4,321.25..4,321.25 rows=66,725 width=32) (actual time=254.548..254.549 rows=66,725 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,963kB
  • Buffers: shared hit=3,654
23. 133.051 133.051 ↑ 1.0 66,725 1

Seq Scan on settlement ps (cost=0.00..4,321.25 rows=66,725 width=32) (actual time=0.177..133.051 rows=66,725 loops=1)

  • Buffers: shared hit=3,654
24. 1.280 3.370 ↑ 1.6 733 1

Hash (cost=512.52..512.52 rows=1,179 width=52) (actual time=3.369..3.370 rows=733 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 72kB
  • Buffers: shared hit=579
25. 2.090 2.090 ↑ 1.0 1,150 1

Index Scan using settlement_settlementtype_id_idx on settlement es (cost=0.29..512.52 rows=1,179 width=52) (actual time=0.022..2.090 rows=1,150 loops=1)

  • Index Cond: (settlementtype_id = 7)
  • Buffers: shared hit=579
26. 5.796 5.796 ↓ 0.0 0 322

Index Scan using "IX_settlement_parentsettlement_id" on settlement rs (cost=0.42..11.88 rows=1 width=56) (actual time=0.018..0.018 rows=0 loops=322)

  • Index Cond: (parentsettlement_id = s.settlement_id)
  • Filter: (settlementtype_id = 2)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1,369
Planning time : 4.426 ms
Execution time : 8,049.838 ms