explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IMa6 : Optimization for: Optimization for: AJX; plan #qBeg; plan #G8iV

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.455 853.599 ↓ 4.9 322 1

Sort (cost=47,749.42..47,749.58 rows=66 width=310) (actual time=853.267..853.599 rows=322 loops=1)

  • Sort Key: s.counttime DESC
  • Sort Method: quicksort Memory: 110kB
  • Buffers: shared hit=81,885
2. 2.877 852.144 ↓ 4.9 322 1

Nested Loop Left Join (cost=867.80..47,747.42 rows=66 width=310) (actual time=7.543..852.144 rows=322 loops=1)

  • Join Filter: (s.settlementtype_id = 8)
  • Buffers: shared hit=81,885
3. 1.883 846.691 ↓ 4.9 322 1

Nested Loop Left Join (cost=867.38..46,960.99 rows=66 width=241) (actual time=7.500..846.691 rows=322 loops=1)

  • Buffers: shared hit=80,516
4. 1.821 15.658 ↓ 4.9 322 1

Nested Loop Left Join (cost=219.43..4,194.05 rows=66 width=105) (actual time=3.303..15.658 rows=322 loops=1)

  • Buffers: shared hit=2,845
5. 10.066 11.905 ↓ 4.9 322 1

Bitmap Heap Scan on settlement s (cost=219.15..4,094.58 rows=66 width=114) (actual time=3.271..11.905 rows=322 loops=1)

  • Recheck Cond: (parentsettlement_id IS NULL)
  • Filter: ((settlementtype_id = 8) AND (COALESCE(source_moneystorage_id, sink_moneystorage_id) = '4a5b7872-31c1-11e9-9fdd-0050568b70b8'::uuid))
  • Rows Removed by Filter: 14,484
  • Heap Blocks: exact=1,835
  • Buffers: shared hit=1,879
6. 1.839 1.839 ↓ 1.0 14,806 1

Bitmap Index Scan on "IX_settlement_parentsettlement_id" (cost=0.00..219.13 rows=14,762 width=0) (actual time=1.838..1.839 rows=14,806 loops=1)

  • Index Cond: (parentsettlement_id IS NULL)
  • Buffers: shared hit=44
7. 1.932 1.932 ↑ 1.0 1 322

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

  • Index Cond: (user_id = s.user_id)
  • Buffers: shared hit=966
8. 7.084 829.150 ↑ 1.0 1 322

Aggregate (cost=647.95..647.96 rows=1 width=136) (actual time=2.574..2.575 rows=1 loops=322)

  • Buffers: shared hit=77,671
9. 8.372 822.066 ↑ 6.7 6 322

Nested Loop Left Join (cost=111.26..647.34 rows=40 width=48) (actual time=2.030..2.553 rows=6 loops=322)

  • Buffers: shared hit=77,671
10. 12.236 595.378 ↓ 1.2 6 322

Nested Loop Left Join (cost=65.26..368.25 rows=5 width=108) (actual time=1.408..1.849 rows=6 loops=322)

  • Buffers: shared hit=70,609
11. 9.338 565.754 ↓ 1.2 6 322

Nested Loop Left Join (cost=62.80..306.47 rows=5 width=88) (actual time=1.387..1.757 rows=6 loops=322)

  • Buffers: shared hit=64,403
12. 5.474 11.592 ↓ 1.2 6 322

Bitmap Heap Scan on settlement cs (cost=4.88..16.74 rows=5 width=60) (actual time=0.025..0.036 rows=6 loops=322)

  • Recheck Cond: ((parentsettlement_id = s.settlement_id) OR (settlement_id = s.settlement_id))
  • Filter: ((settlementtype_id = 8) AND ((parentsettlement_id = s.settlement_id) OR ((parentsettlement_id IS NULL) AND (settlement_id = s.settlement_id))))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=414
  • Buffers: shared hit=2,356
13. 1.610 6.118 ↓ 0.0 0 322

BitmapOr (cost=4.88..4.88 rows=6 width=0) (actual time=0.019..0.019 rows=0 loops=322)

  • Buffers: shared hit=1,942
14. 2.576 2.576 ↑ 1.0 5 322

Bitmap Index Scan on "IX_settlement_parentsettlement_id" (cost=0.00..2.46 rows=5 width=0) (actual time=0.008..0.008 rows=5 loops=322)

  • Index Cond: (parentsettlement_id = s.settlement_id)
  • Buffers: shared hit=973
15. 1.932 1.932 ↑ 1.0 1 322

Bitmap Index Scan on settlement_pkey (cost=0.00..2.42 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=322)

  • Index Cond: (settlement_id = s.settlement_id)
  • Buffers: shared hit=969
16. 5.796 544.824 ↓ 0.0 0 1,932

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

  • Buffers: shared hit=62,047
17. 85.008 539.028 ↓ 0.0 0 1,932

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

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

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

  • Buffers: shared hit=62,047
19. 38.640 92.736 ↓ 0.0 0 1,932

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

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

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

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

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

  • Filter: isphysical
  • Rows Removed by Filter: 30
  • Buffers: shared hit=1,932
22. 211.232 241.822 ↓ 40.0 160 322

Bitmap Heap Scan on settlement s_1 (cost=2.69..54.91 rows=4 width=76) (actual time=0.124..0.751 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
23. 30.590 30.590 ↓ 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.095..0.095 rows=963 loops=322)

  • Index Cond: ((source_moneystorage_id = cs.source_moneystorage_id) AND (counttime < cs.counttime))
  • Buffers: shared hit=2,421
24. 7.728 17.388 ↓ 0.0 0 1,932

Bitmap Heap Scan on settlement es (cost=2.46..12.35 rows=1 width=52) (actual time=0.009..0.009 rows=0 loops=1,932)

  • Recheck Cond: (parentsettlement_id = cs.settlement_id)
  • Filter: (settlementtype_id = 7)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=396
  • Buffers: shared hit=6,206
25. 9.660 9.660 ↑ 5.0 1 1,932

Bitmap Index Scan on "IX_settlement_parentsettlement_id" (cost=0.00..2.46 rows=5 width=0) (actual time=0.005..0.005 rows=1 loops=1,932)

  • Index Cond: (parentsettlement_id = cs.settlement_id)
  • Buffers: shared hit=5,810
26. 7.728 218.316 ↓ 0.0 0 1,932

Nested Loop (cost=46.00..55.81 rows=1 width=68) (actual time=0.113..0.113 rows=0 loops=1,932)

  • Join Filter: CASE WHEN (ps.parentsettlement_id IS NOT NULL) THEN ((ps.parentsettlement_id = s_1.parentsettlement_id) OR ((s_1.parentsettlement_id IS NULL) AND (ps.parentsettlement_id = s_1.settlement_id))) ELSE ((rs_1.parentsettlement_id = s_1.parentsettlement_id) OR ((s_1.parentsettlement_id IS NULL) AND (rs_1.parentsettlement_id = s_1.settlement_id))) END
  • Buffers: shared hit=7,062
27. 5.796 210.588 ↓ 0.0 0 1,932

Bitmap Heap Scan on settlement rs_1 (cost=45.58..53.52 rows=1 width=52) (actual time=0.109..0.109 rows=0 loops=1,932)

  • Recheck Cond: ((source_moneystorage_id = s_1.source_moneystorage_id) AND (settlementtype_id = 2))
  • Filter: (paymenttype_id = s_1.paymenttype_id)
  • Buffers: shared hit=7,062
28. 14.895 204.792 ↓ 0.0 0 1,932

BitmapAnd (cost=45.58..45.58 rows=4 width=0) (actual time=0.106..0.106 rows=0 loops=1,932)

  • Buffers: shared hit=7,062
29. 59.892 59.892 ↓ 3.9 321 1,932

Bitmap Index Scan on "IX_settlement_source_moneystorage_id_counttime" (cost=0.00..3.03 rows=82 width=0) (actual time=0.031..0.031 rows=321 loops=1,932)

  • Index Cond: (source_moneystorage_id = s_1.source_moneystorage_id)
  • Buffers: shared hit=3,852
30. 130.005 130.005 ↓ 1.0 3,147 321

Bitmap Index Scan on settlement_settlementtype_id_idx (cost=0.00..41.53 rows=3,098 width=0) (actual time=0.405..0.405 rows=3,147 loops=321)

  • Index Cond: (settlementtype_id = 2)
  • Buffers: shared hit=3,210
31. 0.000 0.000 ↓ 0.0 0

Index Scan using settlement_pkey on settlement ps (cost=0.42..2.27 rows=1 width=32) (never executed)

  • Index Cond: (settlement_id = rs_1.parentsettlement_id)
32. 2.576 2.576 ↓ 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.008..0.008 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.491 ms
Execution time : 854.295 ms