explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HT8bA : Optimization for: Optimization for: plan #LkCI; plan #ztu

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.488 882.682 ↓ 4.9 322 1

Sort (cost=47,713.12..47,713.28 rows=66 width=310) (actual time=882.401..882.682 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))
  • Sort Key: s.counttime DESC
  • Sort Method: quicksort Memory: 110kB
  • Buffers: shared hit=81,885
2. 3.089 881.194 ↓ 4.9 322 1

Nested Loop Left Join (cost=867.25..47,711.12 rows=66 width=310) (actual time=7.352..881.194 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=81,885
3. 2.236 875.529 ↓ 4.9 322 1

Nested Loop Left Join (cost=866.83..46,924.69 rows=66 width=241) (actual time=7.311..875.529 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=80,516
4. 1.866 15.807 ↓ 4.9 322 1

Nested Loop Left Join (cost=219.43..4,194.05 rows=66 width=105) (actual time=3.263..15.807 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=2,845
5. 10.180 12.009 ↓ 4.9 322 1

Bitmap Heap Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement s (cost=219.15..4,094.58 rows=66 width=114) (actual time=3.230..12.009 rows=322 loops=1)

  • Output: s.settlement_id, s.counttime, s.settlementnote, s.source_moneystorage_id, s."timestamp", s.user_id, s.settlementtype_id
  • Recheck Cond: (s.parentsettlement_id IS NULL)
  • Filter: ((s.settlementtype_id = 8) AND (COALESCE(s.source_moneystorage_id, s.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.829 1.829 ↓ 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.828..1.829 rows=14,806 loops=1)

  • Index Cond: (s.parentsettlement_id IS NULL)
  • Buffers: shared hit=44
7. 1.932 1.932 ↑ 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.006..0.006 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
8. 6.762 857.486 ↑ 1.0 1 322

Aggregate (cost=647.40..647.41 rows=1 width=136) (actual time=2.662..2.663 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=77,671
9. 12.558 850.724 ↓ 1.2 6 322

Nested Loop Left Join (cost=111.26..647.32 rows=5 width=48) (actual time=2.163..2.642 rows=6 loops=322)

  • Output: s_1.sum, rs_1.sum, cs.sum, es.source_moneystorage_id, es.sum, es.settlement_id
  • Buffers: shared hit=77,671
10. 10.626 820.778 ↓ 1.2 6 322

Nested Loop Left Join (cost=108.80..585.54 rows=5 width=28) (actual time=2.139..2.549 rows=6 loops=322)

  • Output: cs.sum, cs.settlement_id, s_1.sum, rs_1.sum
  • Buffers: shared hit=71,465
11. 5.796 12.236 ↓ 1.2 6 322

Bitmap Heap Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement cs (cost=4.88..16.74 rows=5 width=60) (actual time=0.026..0.038 rows=6 loops=322)

  • Output: cs.sum, cs.counttime, cs.paymenttype_id, cs.source_moneystorage_id, cs.settlement_id
  • Recheck Cond: ((cs.parentsettlement_id = s.settlement_id) OR (cs.settlement_id = s.settlement_id))
  • Filter: ((cs.settlementtype_id = 8) AND ((cs.parentsettlement_id = s.settlement_id) OR ((cs.parentsettlement_id IS NULL) AND (cs.settlement_id = s.settlement_id))))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=414
  • Buffers: shared hit=2,356
12. 1.610 6.440 ↓ 0.0 0 322

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

  • Buffers: shared hit=1,942
13. 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: (cs.parentsettlement_id = s.settlement_id)
  • Buffers: shared hit=973
14. 2.254 2.254 ↑ 1.0 1 322

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

  • Index Cond: (cs.settlement_id = s.settlement_id)
  • Buffers: shared hit=969
15. 6.468 797.916 ↓ 0.0 0 1,932

Nested Loop Left Join (cost=103.92..113.75 rows=1 width=8) (actual time=0.413..0.413 rows=0 loops=1,932)

  • Output: s_1.sum, rs_1.sum
  • Buffers: shared hit=69,109
16. 5.796 575.736 ↓ 0.0 0 1,932

Limit (cost=57.92..57.93 rows=1 width=717) (actual time=0.298..0.298 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
17. 86.940 569.940 ↓ 0.0 0 1,932

Sort (cost=57.92..57.93 rows=1 width=717) (actual time=0.295..0.295 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
18. 123.970 483.000 ↓ 27.0 27 1,932

Nested Loop (cost=2.69..57.91 rows=1 width=717) (actual time=0.076..0.250 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
19. 40.572 98.532 ↓ 0.0 0 1,932

Nested Loop (cost=0.00..2.96 rows=1 width=16) (actual time=0.051..0.051 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
20. 15.456 15.456 ↑ 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.006..0.008 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
21. 42.504 42.504 ↑ 1.2 13 1,932

Seq Scan on main.paymenttype mpt (cost=0.00..1.45 rows=15 width=4) (actual time=0.007..0.022 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
22. 228.298 260.498 ↓ 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.132..0.809 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
23. 32.200 32.200 ↓ 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.100..0.100 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
24. 1.926 215.712 ↓ 0.0 0 321

Nested Loop (cost=46.00..55.80 rows=1 width=68) (actual time=0.672..0.672 rows=0 loops=321)

  • Output: rs_1.sum, rs_1.parentsettlement_id, rs_1.paymenttype_id, rs_1.source_moneystorage_id, ps.parentsettlement_id
  • Inner Unique: true
  • Join Filter: ((COALESCE(ps.parentsettlement_id, rs_1.parentsettlement_id) = s_1.parentsettlement_id) OR ((s_1.parentsettlement_id IS NULL) AND (COALESCE(ps.parentsettlement_id, rs_1.parentsettlement_id) = s_1.settlement_id)))
  • Buffers: shared hit=7,062
25. 2.568 213.786 ↓ 0.0 0 321

Bitmap Heap Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement rs_1 (cost=45.58..53.52 rows=1 width=52) (actual time=0.666..0.666 rows=0 loops=321)

  • Output: rs_1.sum, rs_1.parentsettlement_id, rs_1.paymenttype_id, rs_1.source_moneystorage_id
  • Recheck Cond: ((rs_1.source_moneystorage_id = s_1.source_moneystorage_id) AND (rs_1.settlementtype_id = 2))
  • Filter: (rs_1.paymenttype_id = s_1.paymenttype_id)
  • Buffers: shared hit=7,062
26. 13.482 211.218 ↓ 0.0 0 321

BitmapAnd (cost=45.58..45.58 rows=4 width=0) (actual time=0.658..0.658 rows=0 loops=321)

  • Buffers: shared hit=7,062
27. 61.632 61.632 ↓ 23.6 1,935 321

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

  • Index Cond: (rs_1.source_moneystorage_id = s_1.source_moneystorage_id)
  • Buffers: shared hit=3,852
28. 136.104 136.104 ↓ 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.424..0.424 rows=3,147 loops=321)

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

Index Scan using settlement_pkey on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement ps (cost=0.42..2.27 rows=1 width=32) (never executed)

  • Output: ps.settlement_id, ps.parentsettlement_id
  • Index Cond: (ps.settlement_id = rs_1.parentsettlement_id)
30. 7.728 17.388 ↓ 0.0 0 1,932

Bitmap Heap Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".settlement es (cost=2.46..12.35 rows=1 width=52) (actual time=0.009..0.009 rows=0 loops=1,932)

  • Output: es.source_moneystorage_id, es.sum, es.settlement_id, es.parentsettlement_id
  • Recheck Cond: (es.parentsettlement_id = cs.settlement_id)
  • Filter: (es.settlementtype_id = 7)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=396
  • Buffers: shared hit=6,206
31. 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: (es.parentsettlement_id = cs.settlement_id)
  • Buffers: shared hit=5,810
32. 2.576 2.576 ↓ 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.008..0.008 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.585 ms
Execution time : 883.380 ms