explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TiSX : AJX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,005.472 ↑ 1.0 25 1

Limit (cost=1,938,682.21..1,938,682.27 rows=25 width=304) (actual time=8,005.411..8,005.472 rows=25 loops=1)

  • Buffers: shared hit=1,257,438
2.          

CTE ajaxtable_tmp

3. 5.684 8,001.483 ↓ 4.9 322 1

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

  • Join Filter: (s.settlementtype_id = 8)
  • Buffers: shared hit=1,257,438
4. 2.482 7,990.003 ↓ 4.9 322 1

Nested Loop Left Join (cost=29,279.49..1,937,890.44 rows=66 width=241) (actual time=262.089..7,990.003 rows=322 loops=1)

  • Buffers: shared hit=1,256,069
5. 2.067 41.205 ↓ 4.9 322 1

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

  • Buffers: shared hit=11,854
6. 36.562 36.562 ↓ 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.191..36.562 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
7. 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
8. 8.050 7,946.316 ↑ 1.0 1 322

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

  • Buffers: shared hit=1,244,215
9. 5.356 7,938.266 ↑ 50.0 6 322

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

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

Hash Left Join (cost=6,407.16..28,738.87 rows=300 width=28) (actual time=14.102..24.626 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
11. 11.914 7,715.442 ↑ 50.0 6 322

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

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

Seq Scan on settlement cs (cost=0.00..4,654.88 rows=300 width=60) (actual time=12.098..22.316 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
13. 5.796 517.776 ↓ 0.0 0 1,932

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

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

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

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

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

  • Buffers: shared hit=62,047
16. 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
17. 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
18. 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
19. 214.130 248.262 ↓ 40.0 160 322

Bitmap Heap Scan on settlement s_1 (cost=2.69..54.91 rows=4 width=76) (actual time=0.138..0.771 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
20. 34.132 34.132 ↓ 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.106..0.106 rows=963 loops=322)

  • Index Cond: ((source_moneystorage_id = cs.source_moneystorage_id) AND (counttime < cs.counttime))
  • Buffers: shared hit=2,421
21. 1.928 207.959 ↑ 2.2 1,385 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=5,001
22. 5.920 206.031 ↑ 2.2 1,385 1

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

  • Hash Cond: (rs_1.parentsettlement_id = ps.settlement_id)
  • Buffers: shared hit=5,001
23. 5.749 5.749 ↓ 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.040..5.749 rows=3,147 loops=1)

  • Index Cond: (settlementtype_id = 2)
  • Buffers: shared hit=1,347
24. 92.378 194.362 ↑ 1.0 66,725 1

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

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

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

  • Buffers: shared hit=3,654
26. 1.252 3.338 ↑ 1.6 733 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 72kB
  • Buffers: shared hit=579
27. 2.086 2.086 ↑ 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.086 rows=1,150 loops=1)

  • Index Cond: (settlementtype_id = 7)
  • Buffers: shared hit=579
28. 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
29.          

Initplan (for Limit)

30. 0.728 7,742.417 ↑ 1.0 1 1

Aggregate (cost=1.49..1.50 rows=1 width=8) (actual time=7,742.415..7,742.417 rows=1 loops=1)

  • Buffers: shared hit=1,246,227
31. 7,741.689 7,741.689 ↓ 4.9 322 1

CTE Scan on ajaxtable_tmp ajaxtable_tmp_1 (cost=0.00..1.32 rows=66 width=0) (actual time=0.002..7,741.689 rows=322 loops=1)

  • Buffers: shared hit=1,246,227
32. 0.503 8,005.426 ↑ 2.6 25 1

Sort (cost=3.18..3.35 rows=66 width=304) (actual time=8,005.405..8,005.426 rows=25 loops=1)

  • Sort Key: ajaxtable_tmp.col_10 DESC, ajaxtable_tmp.col_0
  • Sort Method: top-N heapsort Memory: 31kB
  • Buffers: shared hit=1,257,438
33. 8,004.923 8,004.923 ↓ 4.9 322 1

CTE Scan on ajaxtable_tmp (cost=0.00..1.32 rows=66 width=304) (actual time=8,004.558..8,004.923 rows=322 loops=1)

  • Buffers: shared hit=1,257,438
Planning time : 5.066 ms
Execution time : 8,005.973 ms