explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EOmj

Settings
# exclusive inclusive rows x rows loops node
1. 5.821 7,968.487 ↓ 4.9 322 1

Nested Loop Left Join (cost=29,279.91..1,938,676.87 rows=66 width=310) (actual time=245.518..7,968.487 rows=322 loops=1)

  • Output: s.settlement_id, (COALESCE((sum(s_1.sum)), '0'::numeric) - COALESCE((sum(rs_1.sum)), '0'::numeric)), (sum(cs.sum)), (sum(CASE WHEN (es.source_moneystorage_id IS NOT NULL) THEN (- es.sum) ELSE es.sum END)), (count(*) FILTER (WHERE (es.settlement_id IS NOT NULL))), ((COALESCE((sum(cs.sum)), '0'::numeric) - COALESCE((sum(CASE WHEN (es.source_moneystorage_id IS NOT NULL) THEN (- es.sum) ELSE es.sum END)), '0'::numeric)) - (COALESCE((sum(s_1.sum)), '0'::numeric) - COALESCE((sum(rs_1.sum)), '0'::numeric))), s.counttime, (((u.firstname)::text || ' '::text) || (u.lastname)::text), s.settlementnote, s.source_moneystorage_id, date_part('epoch'::text, s.counttime), s."timestamp", date_part('epoch'::text, rs."timestamp"), COALESCE(row_to_json((ROW(rs.settlement_id, rs."timestamp"))), '{}'::json)
  • Join Filter: (s.settlementtype_id = 8)
  • Buffers: shared hit=1,257,438
2. 2.461 7,956.870 ↓ 4.9 322 1

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

  • Output: s.settlement_id, s.counttime, s.settlementnote, s.source_moneystorage_id, s."timestamp", s.settlementtype_id, u.firstname, u.lastname, (sum(s_1.sum)), (sum(rs_1.sum)), (sum(cs.sum)), (sum(CASE WHEN (es.source_moneystorage_id IS NOT NULL) THEN (- es.sum) ELSE es.sum END)), (count(*) FILTER (WHERE (es.settlement_id IS NOT NULL)))
  • Buffers: shared hit=1,256,069
3. 2.028 40.615 ↓ 4.9 322 1

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

  • Output: s.settlement_id, s.counttime, s.settlementnote, s.source_moneystorage_id, s."timestamp", s.settlementtype_id, u.firstname, u.lastname
  • Inner Unique: true
  • Buffers: shared hit=11,854
4. 36.011 36.011 ↓ 4.9 322 1

Index Scan Backward using "IX_settlement_counttime" on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement s (cost=0.29..5,380.00 rows=66 width=114) (actual time=7.189..36.011 rows=322 loops=1)

  • Output: s.settlement_id, s.counttime, s.settlementnote, s.source_moneystorage_id, s."timestamp", s.user_id, s.settlementtype_id
  • Filter: ((s.parentsettlement_id IS NULL) AND (s.settlementtype_id = 8) AND (COALESCE(s.source_moneystorage_id, s.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 main."user" u (cost=0.28..1.51 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=322)

  • Output: u.user_id, u.username, u.firstname, u.lastname, u.password, u.salt, u.disabledtime, u.language_id, u.log_id, u.pincode, u.lastlogin, u.avatar, u.usertype_id, u.introduction, u.external_id, u.presystem_id, u.client_id
  • Index Cond: (u.user_id = s.user_id)
  • Buffers: shared hit=966
6. 8.050 7,913.794 ↑ 1.0 1 322

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

  • Output: sum(s_1.sum), sum(rs_1.sum), sum(cs.sum), sum(CASE WHEN (es.source_moneystorage_id IS NOT NULL) THEN (- es.sum) ELSE es.sum END), count(*) FILTER (WHERE (es.settlement_id IS NOT NULL))
  • Buffers: shared hit=1,244,215
7. 5.421 7,905.744 ↑ 50.0 6 322

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

  • Output: s_1.sum, rs_1.sum, cs.sum, es.source_moneystorage_id, es.sum, es.settlement_id
  • Hash Cond: (cs.settlement_id = es.parentsettlement_id)
  • Buffers: shared hit=1,244,215
8. 6.130 7,896.728 ↑ 50.0 6 322

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

  • Output: cs.sum, cs.settlement_id, s_1.sum, rs_1.sum
  • 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. 10.948 7,699.342 ↑ 50.0 6 322

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

  • Output: cs.sum, cs.settlement_id, s_1.sum, s_1.paymenttype_id, s_1.source_moneystorage_id, s_1.parentsettlement_id, s_1.settlement_id
  • Buffers: shared hit=1,238,635
10. 7,176.414 7,176.414 ↑ 50.0 6 322

Seq Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement cs (cost=0.00..4,654.88 rows=300 width=60) (actual time=12.076..22.287 rows=6 loops=322)

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

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

  • Output: s_1.settlement_id, NULL::text, NULL::uuid, NULL::timestamp with time zone, NULL::integer, s_1.source_moneystorage_id, NULL::uuid, s_1.sum, NULL::uuid, NULL::uuid, NULL::bigint, NULL::uuid, s_1.paymenttype_id, NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::character varying(100), s_1.counttime, s_1.parentsettlement_id, NULL::character varying(100), NULL::boolean
  • Buffers: shared hit=62,047
12. 75.348 506.184 ↓ 0.0 0 1,932

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

  • Output: s_1.settlement_id, NULL::text, NULL::uuid, NULL::timestamp with time zone, NULL::integer, s_1.source_moneystorage_id, NULL::uuid, s_1.sum, NULL::uuid, NULL::uuid, NULL::bigint, NULL::uuid, s_1.paymenttype_id, NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::character varying(100), s_1.counttime, s_1.parentsettlement_id, NULL::character varying(100), NULL::boolean
  • Sort Key: s_1.counttime DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=62,047
13. 104.972 430.836 ↓ 27.0 27 1,932

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

  • Output: s_1.settlement_id, NULL::text, NULL::uuid, NULL::timestamp with time zone, NULL::integer, s_1.source_moneystorage_id, NULL::uuid, s_1.sum, NULL::uuid, NULL::uuid, NULL::bigint, NULL::uuid, s_1.paymenttype_id, NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::numeric(15,2), NULL::character varying(100), s_1.counttime, s_1.parentsettlement_id, NULL::character varying(100), NULL::boolean
  • 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.040..0.041 rows=0 loops=1,932)

  • Output: pt.paymenttype_id
  • Inner Unique: true
  • 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 "company2de759bc-3fce-11e3-ac31-5b2986f46e71".paymenttype pt (cost=0.00..1.32 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1,932)

  • Output: pt.paymenttype_id, pt.mainpaymenttype_id, pt.log_id, pt.currency_id, pt.disabledtime, pt.isbase, pt.name, pt.product_id, pt.ledgeraccount_id, pt.invoicecustomer_id
  • Filter: (pt.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 main.paymenttype mpt (cost=0.00..1.45 rows=15 width=4) (actual time=0.006..0.017 rows=13 loops=1,932)

  • Output: mpt.paymenttype_id, mpt.namekey, mpt.bank, mpt.cardprogram_id, mpt.log_id, mpt.iscashreturn, mpt.isphysical, mpt.isactual, mpt.isreturnable, mpt.iscustom, mpt.currency_id, mpt.superpaymenttype_id, mpt.ispurchase, mpt.allownegativepayment, mpt.allowinvoicecustomer, mpt.iscountable
  • Filter: mpt.isphysical
  • Rows Removed by Filter: 30
  • Buffers: shared hit=1,932
17. 212.198 246.652 ↓ 40.0 160 322

Bitmap Heap Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement s_1 (cost=2.69..54.91 rows=4 width=76) (actual time=0.139..0.766 rows=160 loops=322)

  • Output: s_1.settlement_id, s_1.source_moneystorage_id, s_1.sum, s_1.paymenttype_id, s_1.counttime, s_1.parentsettlement_id
  • Recheck Cond: ((s_1.source_moneystorage_id = cs.source_moneystorage_id) AND (s_1.counttime < cs.counttime))
  • Filter: ((s_1.paymenttype_id = cs.paymenttype_id) AND (s_1.settlementtype_id = 8))
  • Rows Removed by Filter: 802
  • Heap Blocks: exact=55,762
  • Buffers: shared hit=58,183
18. 34.454 34.454 ↓ 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.107..0.107 rows=963 loops=322)

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

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

  • Output: rs_1.sum, rs_1.parentsettlement_id, rs_1.paymenttype_id, rs_1.source_moneystorage_id, ps.parentsettlement_id
  • Buckets: 4,096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=5,001
20. 4.837 189.380 ↑ 2.2 1,385 1

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

  • Output: rs_1.sum, rs_1.parentsettlement_id, rs_1.paymenttype_id, rs_1.source_moneystorage_id, ps.parentsettlement_id
  • Inner Unique: true
  • Hash Cond: (rs_1.parentsettlement_id = ps.settlement_id)
  • Buffers: shared hit=5,001
21. 5.772 5.772 ↓ 1.0 3,147 1

Index Scan using settlement_settlementtype_id_idx on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement rs_1 (cost=0.29..1,131.58 rows=3,098 width=52) (actual time=0.038..5.772 rows=3,147 loops=1)

  • Output: rs_1.sum, rs_1.parentsettlement_id, rs_1.paymenttype_id, rs_1.source_moneystorage_id
  • Index Cond: (rs_1.settlementtype_id = 2)
  • Buffers: shared hit=1,347
22. 85.710 178.771 ↑ 1.0 66,725 1

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

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

Seq Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement ps (cost=0.00..4,321.25 rows=66,725 width=32) (actual time=0.143..93.061 rows=66,725 loops=1)

  • Output: ps.settlement_id, ps.parentsettlement_id
  • Buffers: shared hit=3,654
24. 1.539 3.595 ↑ 1.6 733 1

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

  • Output: es.source_moneystorage_id, es.sum, es.settlement_id, es.parentsettlement_id
  • Buckets: 2,048 Batches: 1 Memory Usage: 72kB
  • Buffers: shared hit=579
25. 2.056 2.056 ↑ 1.0 1,150 1

Index Scan using settlement_settlementtype_id_idx on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement es (cost=0.29..512.52 rows=1,179 width=52) (actual time=0.022..2.056 rows=1,150 loops=1)

  • Output: es.source_moneystorage_id, es.sum, es.settlement_id, es.parentsettlement_id
  • Index Cond: (es.settlementtype_id = 7)
  • Buffers: shared hit=579
26. 5.796 5.796 ↓ 0.0 0 322

Index Scan using "IX_settlement_parentsettlement_id" on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement rs (cost=0.42..11.88 rows=1 width=56) (actual time=0.018..0.018 rows=0 loops=322)

  • Output: rs."timestamp", rs.parentsettlement_id, ROW(rs.settlement_id, rs."timestamp")
  • Index Cond: (rs.parentsettlement_id = s.settlement_id)
  • Filter: (rs.settlementtype_id = 2)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1,369
Planning time : 4.479 ms
Execution time : 7,969.428 ms