explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FFgB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.389 147,186.411 ↓ 56.0 56 1

GroupAggregate (cost=111,367.42..111,367.53 rows=1 width=473) (actual time=147,186.198..147,186.411 rows=56 loops=1)

  • Group Key: t4._fld36278rref, t5._fld36300rref, (date_trunc('month'::text, (date_trunc('month'::text, t3._period)))), t4._fld36281_type, t4._fld36281_rtref, t4._fld36281_rrref, t10._fld6859, (sum(t3._fld42604)), (sum(t3._fld42605)), t10._fld6807rref, t10._fld6830, t10._fld6847, t11._fld8224rref
  • Filter: ((sum((sum(t3._fld42592))) <> '0'::numeric) OR (sum((sum(t3._fld42593))) <> '0'::numeric) OR (sum(CASE WHEN (t3._fld42584rref = '\\x8c8d4fb9e9a4c800423c97037b7eee65'::bytea) THEN (sum(t3._fld42593)) WHEN COALESCE(t7._fld30163, false) THEN (((sum(t3._fld42592)) * COALESCE(t8._fld41435, '0'::numeric)))::numeric(15,2) ELSE (sum(t3._fld42595)) END) <> '0'::numeric) OR (sum(CASE WHEN COALESCE(t7._fld30163, false) THEN (((sum(t3._fld42592)) * COALESCE(t8._fld41436, '0'::numeric)))::numeric(15,2) ELSE (sum(t3._fld42597)) END) <> '0'::numeric) OR (((sum((sum(t3._fld42593))) - sum(CASE WHEN (t3._fld42584rref = '\\x8c8d4fb9e9a4c800423c97037b7eee65'::bytea) THEN (sum(t3._fld42593)) WHEN COALESCE(t7._fld30163, false) THEN (((sum(t3._fld42592)) * COALESCE(t8._fld41435, '0'::numeric)))::numeric(15,2) ELSE (sum(t3._fld42595)) END)) - sum(CASE WHEN COALESCE(t7._fld30163, false) THEN (((sum(t3._fld42592)) * COALESCE(t8._fld41436, '0'::numeric)))::numeric(15,2) ELSE (sum(t3._fld42597)) END)) <> '0'::numeric))
2. 0.512 147,186.022 ↓ 56.0 56 1

Sort (cost=111,367.42..111,367.43 rows=1 width=449) (actual time=147,186.016..147,186.022 rows=56 loops=1)

  • Sort Key: t4._fld36278rref, t5._fld36300rref, (date_trunc('month'::text, (date_trunc('month'::text, t3._period)))), t4._fld36281_type, t4._fld36281_rtref, t4._fld36281_rrref, t10._fld6859, (sum(t3._fld42604)), (sum(t3._fld42605)), t10._fld6807rref, t10._fld6830, t10._fld6847, t11._fld8224rref
  • Sort Method: quicksort Memory: 39kB
3. 0.629 147,185.510 ↓ 56.0 56 1

Nested Loop Left Join (cost=106,482.81..111,367.42 rows=1 width=449) (actual time=7,153.565..147,185.510 rows=56 loops=1)

4. 0.757 147,183.873 ↓ 56.0 56 1

Nested Loop Left Join (cost=106,482.70..111,365.77 rows=1 width=419) (actual time=7,153.477..147,183.873 rows=56 loops=1)

  • Join Filter: (COALESCE(t7._fld30163, false) AND (t5._fld36301rref = t8._fld41433rref))
  • Rows Removed by Join Filter: 56
5. 2.109 147,181.044 ↓ 56.0 56 1

Nested Loop Left Join (cost=106,482.53..111,365.54 rows=1 width=469) (actual time=7,153.401..147,181.044 rows=56 loops=1)

  • Join Filter: ((t5._fld36301rref = t6._fld30168rref) AND (date_trunc('month'::text, (date_trunc('month'::text, t3._period))) = date_trunc('month'::text, t7._date_time)))
  • Rows Removed by Join Filter: 672
6. 208.307 147,176.471 ↓ 56.0 56 1

Nested Loop (cost=106,481.08..111,362.50 rows=1 width=468) (actual time=7,153.155..147,176.471 rows=56 loops=1)

  • Join Filter: (t4._fld36282rref = t3._fld42580rref)
  • Rows Removed by Join Filter: 557502
7. 1.078 36.880 ↓ 298.0 298 1

Nested Loop (cost=0.22..206.27 rows=1 width=68) (actual time=0.156..36.880 rows=298 loops=1)

8. 0.732 0.732 ↑ 1.0 70 1

Index Only Scan using tmpind_0 on tt501 t9 (cost=0.06..4.41 rows=70 width=17) (actual time=0.046..0.732 rows=70 loops=1)

  • Heap Fetches: 70
9. 35.070 35.070 ↓ 4.0 4 70

Index Scan using _inforg36277_bydims on _inforg36277 t4 (cost=0.16..2.87 rows=1 width=68) (actual time=0.343..0.501 rows=4 loops=70)

  • Index Cond: ((_fld1675 = '0'::numeric) AND (_fld36278rref = t9._reffieldrref))
  • Filter: (CASE WHEN (_fld36278rref IS NOT NULL) THEN '\\x000000ec'::bytea ELSE NULL::bytea END = '\\x000000ec'::bytea)
10. 5,036.393 146,931.284 ↓ 233.9 1,871 298

Hash Join (cost=106,480.86..111,156.14 rows=8 width=419) (actual time=7.838..493.058 rows=1,871 loops=298)

  • Hash Cond: (t3._fld42582rref = t5._fld36305rref)
11. 141,306.286 141,880.184 ↑ 1.1 74,264 298

HashAggregate (cost=105,984.32..109,707.94 rows=82,747 width=893) (actual time=7.500..476.108 rows=74,264 loops=298)

  • Group Key: date_trunc('month'::text, t3._period), t3._fld42584rref, t3._fld59611rref, t3._fld42585rref, t3._fld42582rref, t3._fld42580rref
  • Filter: ((sum(t3._fld42604) <> '0'::numeric) OR (sum(t3._fld59619) <> '0'::numeric) OR (sum(t3._fld59623) <> '0'::numeric) OR (sum(t3._fld59625) <> '0'::numeric) OR (sum(t3._fld42594) <> '0'::numeric) OR (sum(t3._fld59631) <> '0'::numeric) OR (sum(t3._fld59621) <> '0'::numeric) OR (sum(t3._fld42593) <> '0'::numeric) OR (sum(t3._fld42595) <> '0'::numeric) OR (sum(t3._fld42599) <> '0'::numeric) OR (sum(t3._fld42600) <> '0'::numeric) OR (sum(t3._fld42596) <> '0'::numeric) OR (sum(t3._fld59628) <> '0'::numeric) OR (sum(t3._fld59629) <> '0'::numeric) OR (sum(t3._fld59622) <> '0'::numeric) OR (sum(t3._fld42598) <> '0'::numeric) OR (sum(t3._fld59626) <> '0'::numeric) OR (sum(t3._fld59630) <> '0'::numeric) OR (sum(t3._fld59616) <> '0'::numeric) OR (sum(t3._fld59620) <> '0'::numeric) OR (sum(t3._fld42597) <> '0'::numeric) OR (sum(t3._fld42605) <> '0'::numeric) OR (sum(t3._fld59624) <> '0'::numeric) OR (sum(t3._fld42592) <> '0'::numeric) OR (sum(t3._fld59627) <> '0'::numeric))
  • Rows Removed by Filter: 4
12. 573.898 573.898 ↑ 1.4 122,120 1

Index Scan using _accumrg42619_bydims on _accumrgtn42619 t3 (cost=0.27..99,210.32 rows=169,350 width=189) (actual time=0.083..573.898 rows=122,120 loops=1)

  • Index Cond: ((_fld1675 = '0'::numeric) AND (_period >= '2018-10-01 00:00:00'::timestamp without time zone) AND (_period < '2018-12-01 00:00:00'::timestamp without time zone))
  • Filter: ((_fld42604 <> '0'::numeric) OR (_fld59619 <> '0'::numeric) OR (_fld59623 <> '0'::numeric) OR (_fld59625 <> '0'::numeric) OR (_fld42594 <> '0'::numeric) OR (_fld59631 <> '0'::numeric) OR (_fld59621 <> '0'::numeric) OR (_fld42593 <> '0'::numeric) OR (_fld42595 <> '0'::numeric) OR (_fld42599 <> '0'::numeric) OR (_fld42600 <> '0'::numeric) OR (_fld42596 <> '0'::numeric) OR (_fld59628 <> '0'::numeric) OR (_fld59629 <> '0'::numeric) OR (_fld59622 <> '0'::numeric) OR (_fld42598 <> '0'::numeric) OR (_fld59626 <> '0'::numeric) OR (_fld59630 <> '0'::numeric) OR (_fld59616 <> '0'::numeric) OR (_fld59620 <> '0'::numeric) OR (_fld42597 <> '0'::numeric) OR (_fld42605 <> '0'::numeric) OR (_fld59624 <> '0'::numeric) OR (_fld42592 <> '0'::numeric) OR (_fld59627 <> '0'::numeric))
13. 0.073 14.707 ↓ 167.0 167 1

Hash (cost=496.53..496.53 rows=1 width=74) (actual time=14.707..14.707 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
14. 0.078 14.634 ↓ 167.0 167 1

Nested Loop Left Join (cost=0.11..496.53 rows=1 width=74) (actual time=0.067..14.634 rows=167 loops=1)

15. 13.888 13.888 ↓ 167.0 167 1

Seq Scan on _inforg36299 t5 (cost=0.00..494.20 rows=1 width=57) (actual time=0.034..13.888 rows=167 loops=1)

  • Filter: ((_fld36300rref <> '\\xbec5d4c9ef6c1a0f11e63c6dc87148a6'::bytea) AND (_fld1675 = '0'::numeric) AND (_fld36301rref = '\\xbec5d4c9ef6c1a0f11e63e95d4dfd7ec'::bytea) AND (CASE WHEN ((_fld36302_type = '\\x08'::bytea) AND (_fld36302_rtref = '\\x000000cd'::bytea)) THEN _fld36302_type ELSE NULL::bytea END = '\\x08'::bytea) AND (CASE WHEN ((_fld36302_type = '\\x08'::bytea) AND (_fld36302_rtref = '\\x000000cd'::bytea)) THEN _fld36302_rtref ELSE NULL::bytea END = '\\x000000cd'::bytea) AND (CASE WHEN ((_fld36302_type = '\\x08'::bytea) AND (_fld36302_rtref = '\\x000000cd'::bytea)) THEN _fld36302_rrref ELSE NULL::bytea END = '\\xb8c5d43d7eff79bb11e669db84a31795'::bytea))
  • Rows Removed by Filter: 10744
16. 0.668 0.668 ↑ 1.0 1 167

Index Scan using _reference288_s_hpk on _reference288 t11 (cost=0.11..2.33 rows=1 width=34) (actual time=0.004..0.004 rows=1 loops=167)

  • Index Cond: ((_fld1675 = '0'::numeric) AND (t5._fld36300rref = _idrref))
17. 0.827 2.464 ↓ 1.1 13 56

Hash Join (cost=1.46..2.88 rows=12 width=27) (actual time=0.031..0.044 rows=13 loops=56)

  • Hash Cond: (t7._idrref = t6._document823_idrref)
18. 1.568 1.568 ↓ 1.1 26 56

Seq Scan on _document823 t7 (cost=0.00..1.26 rows=24 width=28) (actual time=0.018..0.028 rows=26 loops=56)

  • Filter: (_posted AND (_fld1675 = '0'::numeric))
19. 0.018 0.069 ↑ 1.0 13 1

Hash (cost=1.31..1.31 rows=13 width=36) (actual time=0.069..0.069 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.051 0.051 ↑ 1.0 13 1

Seq Scan on _document823_vt30166 t6 (cost=0.00..1.31 rows=13 width=36) (actual time=0.043..0.051 rows=13 loops=1)

  • Filter: ((_fld30168rref = '\\xbec5d4c9ef6c1a0f11e63e95d4dfd7ec'::bytea) AND (_fld1675 = '0'::numeric))
  • Rows Removed by Filter: 13
21. 2.072 2.072 ↑ 1.0 1 56

Index Scan using _inforg41430_4 on _inforg41430 t8 (cost=0.17..0.22 rows=1 width=96) (actual time=0.036..0.037 rows=1 loops=56)

  • Index Cond: ((_fld1675 = '0'::numeric) AND (t3._fld42580rref = _fld41431rref) AND (_period = date_trunc('month'::text, (date_trunc('month'::text, t3._period)))) AND (t3._fld42585rref = _fld41432rref) AND (_fld41433rref = '\\xbec5d4c9ef6c1a0f11e63e95d4dfd7ec'::bytea) AND (t3._fld59611rref = _fld41434rref))
22. 1.008 1.008 ↑ 1.0 1 56

Index Scan using _reference236_s_hpk on _reference236 t10 (cost=0.11..1.65 rows=1 width=47) (actual time=0.018..0.018 rows=1 loops=56)

  • Index Cond: ((_fld1675 = '0'::numeric) AND (t4._fld36278rref = _idrref))