explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YPpZ : Optimization for: plan #JVHu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7.228 10,266.184 ↑ 27.1 3,796 1

Sort (cost=704,600.87..704,857.67 rows=102,719 width=100) (actual time=10,265.864..10,266.184 rows=3,796 loops=1)

  • Output: i.modelid, i.accountnum, a.name, a.typecode, a.classcode, i.scenarionum, (CASE WHEN ((i.pdnum)::integer = 0) THEN cm.cp_date ELSE (((cm.cp_date + '1 day'::interval) + make_interval(0, ((i.pdnum)::integer - 1), 0, 0, 0, 0, '0'::double precision)))::date END), i.pdnum, i.fiscalyear, (((((((date_part('month'::text, (CASE WHEN ((i.pdnum)::integer = 0) THEN cm.cp_date ELSE (((cm.cp_date + '1 day'::interval) + make_interval(0, ((i.pdnum)::integer - 1), 0, 0, 0, 0, '0'::double precision)))::date END)::timestamp without time zone))::integer + 12) - (cm.yearendmonth)::integer) % 12) / 3) + 1)), (((((((date_part('month'::text, (cm.cp_date)::timestamp without time zone))::integer + (i.pdnum)::integer) - 1) % 12) + 1) = (cm.yearendmonth)::integer)), (((COALESCE(i_1."Balance", m."Balance")) * '0.001'::double precision)), (("*SELECT* 1".balance * '0.001'::double precision)), ((COALESCE("*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance")), '0'::double precision) * '0.001'::double precision)), i.accrual, (((COALESCE("*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance")), '0'::double precision) * '0.001'::double precision) + i.accrual))
  • Sort Key: i.modelid, i.accountnum, i.pdnum
  • Sort Method: quicksort Memory: 658kB
  • Buffers: shared hit=41517 read=19042, temp read=23360 written=24240
2. 188.802 10,258.956 ↑ 27.1 3,796 1

Hash Join (cost=593,531.40..696,050.37 rows=102,719 width=100) (actual time=6,916.483..10,258.956 rows=3,796 loops=1)

  • Output: i.modelid, i.accountnum, a.name, a.typecode, a.classcode, i.scenarionum, CASE WHEN ((i.pdnum)::integer = 0) THEN cm.cp_date ELSE (((cm.cp_date + '1 day'::interval) + make_interval(0, ((i.pdnum)::integer - 1), 0, 0, 0, 0, '0'::double precision)))::date END, i.pdnum, i.fiscalyear, ((((((date_part('month'::text, (CASE WHEN ((i.pdnum)::integer = 0) THEN cm.cp_date ELSE (((cm.cp_date + '1 day'::interval) + make_interval(0, ((i.pdnum)::integer - 1), 0, 0, 0, 0, '0'::double precision)))::date END)::timestamp without time zone))::integer + 12) - (cm.yearendmonth)::integer) % 12) / 3) + 1), ((((((date_part('month'::text, (cm.cp_date)::timestamp without time zone))::integer + (i.pdnum)::integer) - 1) % 12) + 1) = (cm.yearendmonth)::integer), ((COALESCE(i_1."Balance", m."Balance")) * '0.001'::double precision), ("*SELECT* 1".balance * '0.001'::double precision), (COALESCE("*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance")), '0'::double precision) * '0.001'::double precision), i.accrual, ((COALESCE("*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance")), '0'::double precision) * '0.001'::double precision) + i.accrual)
  • Hash Cond: (cm.modelid = m_6.id)
  • Buffers: shared hit=41517 read=19042, temp read=23360 written=24240
3. 1,153.505 10,069.426 ↑ 1.0 1,718,110 1

Hash Join (cost=593,435.38..678,020.23 rows=1,769,342 width=89) (actual time=6,725.237..10,069.426 rows=1,718,110 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, i.modelid, i.accountnum, i.scenarionum, i.pdnum, i.fiscalyear, i.accrual, a.name, a.typecode, a.classcode, a.modelid, "*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance"))
  • Inner Unique: true
  • Hash Cond: ((cm.modelid = a.modelid) AND (i.accountnum = a.accountnum))
  • Buffers: shared hit=41472 read=19042, temp read=23360 written=24240
4. 368.573 8,483.026 ↑ 1.0 1,718,110 1

Merge Left Join (cost=569,401.83..605,830.60 rows=1,769,342 width=50) (actual time=6,288.341..8,483.026 rows=1,718,110 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, i.modelid, i.accountnum, i.scenarionum, i.pdnum, i.fiscalyear, i.accrual, "*SELECT* 1".balance, (COALESCE(i_1."Balance", m."Balance"))
  • Merge Cond: ((i.accountnum = "*SELECT* 1".accountnum) AND ((cm.charter_id)::text = ("*SELECT* 1"."Charter Number")::text) AND (cm.cp_date = "*SELECT* 1"."M/E date"))
  • Join Filter: (i.pdnum = 0)
  • Rows Removed by Join Filter: 148519
  • Buffers: shared hit=34003 read=19042, temp read=14264 written=15144
5. 1,018.740 7,588.720 ↑ 1.0 1,718,110 1

Merge Left Join (cost=489,888.10..512,842.96 rows=1,769,342 width=90) (actual time=5,774.747..7,588.720 rows=1,718,110 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, cm.charter_id, i.modelid, i.accountnum, i.scenarionum, i.pdnum, i.fiscalyear, i.accrual, (COALESCE(i_1."Balance", m."Balance"))
  • Merge Cond: ((i.accountnum = m."FICOM GL") AND ((cm.charter_id)::text = (m."Charter Number")::text) AND (cm.cp_date = m."M/E date"))
  • Join Filter: (i.pdnum = 0)
  • Rows Removed by Join Filter: 1387187
  • Buffers: shared hit=7833 read=19042, temp read=14264 written=15144
6. 2,494.183 3,671.112 ↑ 1.0 1,718,110 1

Sort (cost=337,735.63..342,158.98 rows=1,769,342 width=82) (actual time=2,991.476..3,671.112 rows=1,718,110 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, cm.charter_id, i.modelid, i.accountnum, i.scenarionum, i.pdnum, i.fiscalyear, i.accrual
  • Sort Key: i.accountnum, cm.charter_id, cm.cp_date
  • Sort Method: external merge Disk: 87600kB
  • Buffers: shared hit=1286 read=19042, temp read=10950 written=10955
7. 586.663 1,176.929 ↑ 1.0 1,718,110 1

Hash Join (cost=118.19..69,455.60 rows=1,769,342 width=82) (actual time=3.113..1,176.929 rows=1,718,110 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, cm.charter_id, i.modelid, i.accountnum, i.scenarionum, i.pdnum, i.fiscalyear, i.accrual
  • Hash Cond: (i.modelid = cm.modelid)
  • Buffers: shared hit=1286 read=19042
8. 587.304 587.304 ↑ 1.0 1,864,995 1

Seq Scan on public.empyreanisresults i (cost=0.00..44,575.61 rows=1,884,901 width=24) (actual time=0.101..587.304 rows=1,864,995 loops=1)

  • Output: i.modelid, i.accountnum, i.pdnum, i.scenarionum, i.accrual, i.cash, i.chgid, i.istarget, i.fiscalyear
  • Filter: (i.scenarionum = 0)
  • Rows Removed by Filter: 67715
  • Buffers: shared hit=1262 read=19042
9. 0.449 2.962 ↓ 1.7 1,142 1

Hash (cost=109.57..109.57 rows=689 width=58) (actual time=2.962..2.962 rows=1,142 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, cm.charter_id
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71kB
  • Buffers: shared hit=24
10. 0.203 2.513 ↓ 1.7 1,142 1

Subquery Scan on cm (cost=1.52..109.57 rows=689 width=58) (actual time=0.108..2.513 rows=1,142 loops=1)

  • Output: cm.cp_date, cm.yearendmonth, cm.modelid, cm.charter_id
  • Buffers: shared hit=24
11. 1.966 2.310 ↓ 1.7 1,142 1

Hash Join (cost=1.52..102.68 rows=689 width=1,756) (actual time=0.106..2.310 rows=1,142 loops=1)

  • Output: m_1.id, m_1.cp_date, NULL::character varying(1024), NULL::character varying(255), NULL::boolean, NULL::character(1), NULL::boolean, NULL::integer, c.charter_id, NULL::character varying(255), NULL::character varying(31), c.yearendmonth, NULL::text, NULL::integer, NULL::integer, NULL::date, (((make_date(((date_part('year'::text, (m_1.cp_date)::timestamp without time zone))::integer + CASE WHEN (date_part('month'::text, (m_1.cp_date)::timestamp without time zone) <= ((c.yearendmonth)::integer)::double precision) THEN 0 ELSE 1 END), 1, 1) + make_interval(0, (((c.yearendmonth)::integer - 11) - 1), 0, 0, 0, 0, '0'::double precision)) + '00:00:00'::interval))::date, NULL::date, (((make_date((((date_part('year'::text, (m_1.cp_date)::timestamp without time zone))::integer + CASE WHEN (date_part('month'::text, (m_1.cp_date)::timestamp without time zone) <= ((c.yearendmonth)::integer)::double precision) THEN 0 ELSE 1 END) - 1), 1, 1) + make_interval(0, (((c.yearendmonth)::integer - 11) - 1), 0, 0, 0, 0, '0'::double precision)) + '00:00:00'::interval))::date
  • Inner Unique: true
  • Hash Cond: (m_1.clientid = c.id)
  • Buffers: shared hit=24
12. 0.305 0.305 ↑ 1.0 1,316 1

Seq Scan on public.empyreanmodels m_1 (cost=0.00..36.78 rows=1,378 width=12) (actual time=0.016..0.305 rows=1,316 loops=1)

  • Output: m_1.id, m_1.clientid, m_1.cp_date, m_1.modeldbpath, m_1.periodcode, m_1.isadhocmodel, m_1.isbudget, m_1.modelsqlserverdbname
  • Buffers: shared hit=23
13. 0.014 0.039 ↓ 1.3 21 1

Hash (cost=1.32..1.32 rows=16 width=54) (actual time=0.038..0.039 rows=21 loops=1)

  • Output: c.charter_id, c.yearendmonth, c.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
14. 0.025 0.025 ↓ 1.3 21 1

Seq Scan on public.clients c (cost=0.00..1.32 rows=16 width=54) (actual time=0.015..0.025 rows=21 loops=1)

  • Output: c.charter_id, c.yearendmonth, c.id
  • Filter: c.iscurrentclient
  • Rows Removed by Filter: 11
  • Buffers: shared hit=1
15. 175.719 2,898.868 ↓ 2.0 2,051,157 1

Materialize (cost=147,815.08..152,919.59 rows=1,020,902 width=20) (actual time=2,535.423..2,898.868 rows=2,051,157 loops=1)

  • Output: m."FICOM GL", m."M/E date", m."Charter Number", (COALESCE(i_1."Balance", m."Balance"))
  • Buffers: shared hit=6547, temp read=3314 written=4189
16. 1,982.631 2,723.149 ↑ 1.5 662,597 1

Sort (cost=147,815.08..150,367.33 rows=1,020,902 width=20) (actual time=2,535.414..2,723.149 rows=662,597 loops=1)

  • Output: m."FICOM GL", m."M/E date", m."Charter Number", (COALESCE(i_1."Balance", m."Balance"))
  • Sort Key: m."FICOM GL", m."Charter Number", m."M/E date
  • Sort Method: external merge Disk: 33488kB
  • Buffers: shared hit=6547, temp read=3314 written=4189
17. 272.035 740.518 ↑ 1.0 1,016,195 1

Hash Left Join (cost=73.88..24,984.84 rows=1,020,902 width=20) (actual time=1.496..740.518 rows=1,016,195 loops=1)

  • Output: m."FICOM GL", m."M/E date", m."Charter Number", COALESCE(i_1."Balance", m."Balance")
  • Inner Unique: true
  • Hash Cond: ((m."FICOM GL" = i_1."FICOM GL") AND ((m."Charter Number")::text = (i_1."Charter Number")::text))
  • Join Filter: (m."M/E date" = i_1."M/E date")
  • Rows Removed by Join Filter: 124993
  • Buffers: shared hit=6547
18. 316.090 467.401 ↑ 1.0 1,016,195 1

Hash Join (cost=21.51..19,572.73 rows=1,020,902 width=20) (actual time=0.399..467.401 rows=1,016,195 loops=1)

  • Output: m."FICOM GL", m."M/E date", m."Charter Number", m."Balance
  • Inner Unique: true
  • Hash Cond: (m."FICOM GL" = g."Codes")
  • Buffers: shared hit=6535
19. 150.980 150.980 ↑ 1.0 1,023,086 1

Seq Scan on public."MFSR" m (cost=0.00..16,826.83 rows=1,030,083 width=20) (actual time=0.034..150.980 rows=1,023,086 loops=1)

  • Output: m."Charter Number", m."M/E date", m."FICOM GL", m."Balance
  • Buffers: shared hit=6526
20. 0.145 0.331 ↑ 1.0 556 1

Hash (cost=14.56..14.56 rows=556 width=4) (actual time=0.331..0.331 rows=556 loops=1)

  • Output: g."Codes
  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=9
21. 0.186 0.186 ↑ 1.0 556 1

Seq Scan on public.mfsr_gls_descriptions g (cost=0.00..14.56 rows=556 width=4) (actual time=0.013..0.186 rows=556 loops=1)

  • Output: g."Codes
  • Buffers: shared hit=9
22. 0.600 1.082 ↑ 1.0 1,602 1

Hash (cost=28.15..28.15 rows=1,615 width=25) (actual time=1.082..1.082 rows=1,602 loops=1)

  • Output: i_1."M/E date", i_1."FICOM GL", i_1."Charter Number", i_1."Balance
  • Buckets: 2048 Batches: 1 Memory Usage: 110kB
  • Buffers: shared hit=12
23. 0.482 0.482 ↑ 1.0 1,602 1

Seq Scan on public."InternalFinancialMFSROverrides" i_1 (cost=0.00..28.15 rows=1,615 width=25) (actual time=0.013..0.482 rows=1,602 loops=1)

  • Output: i_1."M/E date", i_1."FICOM GL", i_1."Charter Number", i_1."Balance
  • Buffers: shared hit=12
24. 41.126 525.733 ↓ 10.2 163,588 1

Sort (cost=79,513.74..79,553.66 rows=15,968 width=20) (actual time=513.485..525.733 rows=163,588 loops=1)

  • Output: "*SELECT* 1".balance, "*SELECT* 1".accountnum, "*SELECT* 1"."Charter Number", "*SELECT* 1"."M/E date
  • Sort Key: "*SELECT* 1".accountnum, "*SELECT* 1"."Charter Number", "*SELECT* 1"."M/E date
  • Sort Method: quicksort Memory: 1554kB
  • Buffers: shared hit=26170
25. 2.068 484.607 ↑ 1.1 15,010 1

Append (cost=73.88..78,398.94 rows=15,968 width=20) (actual time=0.849..484.607 rows=15,010 loops=1)

  • Buffers: shared hit=26170
26. 1.107 138.439 ↑ 1.1 7,505 1

Subquery Scan on *SELECT* 1 (cost=73.88..19,646.87 rows=8,372 width=20) (actual time=0.847..138.439 rows=7,505 loops=1)

  • Output: "*SELECT* 1".balance, "*SELECT* 1".accountnum, "*SELECT* 1"."Charter Number", "*SELECT* 1"."M/E date
  • Buffers: shared hit=6547
27. 6.228 137.332 ↑ 1.1 7,505 1

Hash Left Join (cost=73.88..19,563.15 rows=8,372 width=20) (actual time=0.845..137.332 rows=7,505 loops=1)

  • Output: m_2."Charter Number", m_2."M/E date", (m_2."FICOM GL" - 10), COALESCE(i_2."Balance", m_2."Balance")
  • Inner Unique: true
  • Hash Cond: ((m_2."FICOM GL" = i_2."FICOM GL") AND ((m_2."Charter Number")::text = (i_2."Charter Number")::text))
  • Join Filter: (m_2."M/E date" = i_2."M/E date")
  • Rows Removed by Join Filter: 18245
  • Buffers: shared hit=6547
28. 2.516 130.526 ↑ 1.1 7,505 1

Hash Join (cost=21.51..19,445.89 rows=8,372 width=20) (actual time=0.256..130.526 rows=7,505 loops=1)

  • Output: m_2."Charter Number", m_2."M/E date", m_2."FICOM GL", m_2."Balance
  • Inner Unique: true
  • Hash Cond: (m_2."FICOM GL" = g_1."Codes")
  • Buffers: shared hit=6535
29. 127.809 127.809 ↑ 1.1 7,505 1

Seq Scan on public."MFSR" m_2 (cost=0.00..19,402.04 rows=8,447 width=20) (actual time=0.040..127.809 rows=7,505 loops=1)

  • Output: m_2."Charter Number", m_2."M/E date", m_2."FICOM GL", m_2."Balance
  • Filter: (m_2."FICOM GL" = ANY ('{3110,3300}'::integer[]))
  • Rows Removed by Filter: 1015581
  • Buffers: shared hit=6526
30. 0.085 0.201 ↑ 1.0 556 1

Hash (cost=14.56..14.56 rows=556 width=4) (actual time=0.201..0.201 rows=556 loops=1)

  • Output: g_1."Codes
  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=9
31. 0.116 0.116 ↑ 1.0 556 1

Seq Scan on public.mfsr_gls_descriptions g_1 (cost=0.00..14.56 rows=556 width=4) (actual time=0.030..0.116 rows=556 loops=1)

  • Output: g_1."Codes
  • Buffers: shared hit=9
32. 0.315 0.578 ↑ 1.0 1,602 1

Hash (cost=28.15..28.15 rows=1,615 width=25) (actual time=0.577..0.578 rows=1,602 loops=1)

  • Output: i_2."Balance", i_2."M/E date", i_2."FICOM GL", i_2."Charter Number
  • Buckets: 2048 Batches: 1 Memory Usage: 107kB
  • Buffers: shared hit=12
33. 0.263 0.263 ↑ 1.0 1,602 1

Seq Scan on public."InternalFinancialMFSROverrides" i_2 (cost=0.00..28.15 rows=1,615 width=25) (actual time=0.011..0.263 rows=1,602 loops=1)

  • Output: i_2."Balance", i_2."M/E date", i_2."FICOM GL", i_2."Charter Number
  • Buffers: shared hit=12
34. 0.697 120.143 ↑ 1.1 3,753 1

Subquery Scan on *SELECT* 2 (cost=35.91..19,551.76 rows=4,189 width=20) (actual time=0.328..120.143 rows=3,753 loops=1)

  • Output: "*SELECT* 2".balance, "*SELECT* 2".accountnum, "*SELECT* 2"."Charter Number", "*SELECT* 2"."M/E date
  • Buffers: shared hit=6541
35. 6.394 119.446 ↑ 1.1 3,753 1

Hash Left Join (cost=35.91..19,509.87 rows=4,189 width=20) (actual time=0.326..119.446 rows=3,753 loops=1)

  • Output: m_3."Charter Number", m_3."M/E date", 3200, COALESCE(i_3."Balance", m_3."Balance")
  • Inner Unique: true
  • Hash Cond: ((m_3."FICOM GL" = i_3."FICOM GL") AND ((m_3."Charter Number")::text = (i_3."Charter Number")::text))
  • Join Filter: (m_3."M/E date" = i_3."M/E date")
  • Rows Removed by Join Filter: 16256
  • Buffers: shared hit=6541
36. 0.845 112.797 ↑ 1.1 3,753 1

Nested Loop (cost=0.28..19,452.22 rows=4,189 width=20) (actual time=0.052..112.797 rows=3,753 loops=1)

  • Output: m_3."Charter Number", m_3."M/E date", m_3."Balance", m_3."FICOM GL
  • Buffers: shared hit=6529
37. 0.026 0.026 ↑ 1.0 1 1

Index Only Scan using mfsr_gls_descriptions_pk on public.mfsr_gls_descriptions g_2 (cost=0.28..8.29 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1)

  • Output: g_2."Codes
  • Index Cond: (g_2."Codes" = 3300)
  • Heap Fetches: 1
  • Buffers: shared hit=3
38. 111.926 111.926 ↑ 1.1 3,753 1

Seq Scan on public."MFSR" m_3 (cost=0.00..19,402.04 rows=4,189 width=20) (actual time=0.027..111.926 rows=3,753 loops=1)

  • Output: m_3."Charter Number", m_3."M/E date", m_3."FICOM GL", m_3."Balance
  • Filter: (m_3."FICOM GL" = 3300)
  • Rows Removed by Filter: 1019333
  • Buffers: shared hit=6526
39. 0.048 0.255 ↑ 1.1 215 1

Hash (cost=32.19..32.19 rows=230 width=25) (actual time=0.255..0.255 rows=215 loops=1)

  • Output: i_3."Balance", i_3."M/E date", i_3."FICOM GL", i_3."Charter Number
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=12
40. 0.207 0.207 ↑ 1.1 215 1

Seq Scan on public."InternalFinancialMFSROverrides" i_3 (cost=0.00..32.19 rows=230 width=25) (actual time=0.025..0.207 rows=215 loops=1)

  • Output: i_3."Balance", i_3."M/E date", i_3."FICOM GL", i_3."Charter Number
  • Filter: (i_3."FICOM GL" = 3300)
  • Rows Removed by Filter: 1387
  • Buffers: shared hit=12
41. 0.648 223.957 ↓ 1.1 3,752 1

Subquery Scan on *SELECT* 3 (cost=19,533.50..39,120.48 rows=3,407 width=20) (actual time=112.496..223.957 rows=3,752 loops=1)

  • Output: "*SELECT* 3".balance, "*SELECT* 3".accountnum, "*SELECT* 3"."Charter Number", "*SELECT* 3"."M/E date
  • Buffers: shared hit=13082
42. 1.571 223.309 ↓ 1.1 3,752 1

Hash Left Join (cost=19,533.50..39,086.41 rows=3,407 width=20) (actual time=112.495..223.309 rows=3,752 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", 3000, (COALESCE(i_4."Balance", m_4."Balance") - COALESCE(i_5."Balance", m_5."Balance"))
  • Inner Unique: true
  • Hash Cond: ((m_5."FICOM GL" = i_5."FICOM GL") AND ((m_5."Charter Number")::text = (i_5."Charter Number")::text))
  • Join Filter: (m_5."M/E date" = i_5."M/E date")
  • Rows Removed by Join Filter: 1989
  • Buffers: shared hit=13082
43. 0.694 221.603 ↓ 1.1 3,752 1

Nested Loop (cost=19,501.06..39,027.54 rows=3,407 width=44) (actual time=112.350..221.603 rows=3,752 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", i_4."Balance", m_5."Balance", m_5."FICOM GL", m_5."M/E date", m_5."Charter Number
  • Buffers: shared hit=13070
44. 0.019 0.019 ↑ 1.0 1 1

Index Only Scan using mfsr_gls_descriptions_pk on public.mfsr_gls_descriptions g_4 (cost=0.28..8.29 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Output: g_4."Codes
  • Index Cond: (g_4."Codes" = 3110)
  • Heap Fetches: 1
  • Buffers: shared hit=3
45. 4.052 220.890 ↓ 1.1 3,752 1

Hash Left Join (cost=19,500.78..38,985.18 rows=3,407 width=44) (actual time=112.327..220.890 rows=3,752 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", i_4."Balance", m_5."Balance", m_5."FICOM GL", m_5."M/E date", m_5."Charter Number
  • Inner Unique: true
  • Hash Cond: ((m_4."FICOM GL" = i_4."FICOM GL") AND ((m_4."Charter Number")::text = (i_4."Charter Number")::text))
  • Join Filter: (m_4."M/E date" = i_4."M/E date")
  • Rows Removed by Join Filter: 16256
  • Buffers: shared hit=13067
46. 0.666 216.598 ↓ 1.1 3,752 1

Nested Loop (cost=19,465.15..38,931.64 rows=3,407 width=40) (actual time=112.052..216.598 rows=3,752 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", m_4."FICOM GL", m_5."Balance", m_5."FICOM GL", m_5."M/E date", m_5."Charter Number
  • Buffers: shared hit=13055
47. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using mfsr_gls_descriptions_pk on public.mfsr_gls_descriptions g_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)

  • Output: g_3."Codes
  • Index Cond: (g_3."Codes" = 3300)
  • Heap Fetches: 1
  • Buffers: shared hit=3
48. 2.340 215.924 ↓ 1.1 3,752 1

Hash Join (cost=19,464.87..38,889.28 rows=3,407 width=40) (actual time=112.045..215.924 rows=3,752 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", m_4."FICOM GL", m_5."Balance", m_5."FICOM GL", m_5."M/E date", m_5."Charter Number
  • Inner Unique: true
  • Hash Cond: ((m_5."M/E date" = m_4."M/E date") AND ((m_5."Charter Number")::text = (m_4."Charter Number")::text))
  • Buffers: shared hit=13052
49. 101.595 101.595 ↑ 1.1 3,752 1

Seq Scan on public."MFSR" m_5 (cost=0.00..19,402.04 rows=4,258 width=20) (actual time=0.027..101.595 rows=3,752 loops=1)

  • Output: m_5."Charter Number", m_5."M/E date", m_5."FICOM GL", m_5."Balance
  • Filter: (m_5."FICOM GL" = 3110)
  • Rows Removed by Filter: 1019334
  • Buffers: shared hit=6526
50. 1.779 111.989 ↑ 1.1 3,753 1

Hash (cost=19,402.04..19,402.04 rows=4,189 width=20) (actual time=111.989..111.989 rows=3,753 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", m_4."FICOM GL
  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
  • Buffers: shared hit=6526
51. 110.210 110.210 ↑ 1.1 3,753 1

Seq Scan on public."MFSR" m_4 (cost=0.00..19,402.04 rows=4,189 width=20) (actual time=0.016..110.210 rows=3,753 loops=1)

  • Output: m_4."Charter Number", m_4."M/E date", m_4."Balance", m_4."FICOM GL
  • Filter: (m_4."FICOM GL" = 3300)
  • Rows Removed by Filter: 1019333
  • Buffers: shared hit=6526
52. 0.047 0.240 ↑ 1.1 215 1

Hash (cost=32.19..32.19 rows=230 width=25) (actual time=0.239..0.240 rows=215 loops=1)

  • Output: i_4."Balance", i_4."M/E date", i_4."FICOM GL", i_4."Charter Number
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=12
53. 0.193 0.193 ↑ 1.1 215 1

Seq Scan on public."InternalFinancialMFSROverrides" i_4 (cost=0.00..32.19 rows=230 width=25) (actual time=0.016..0.193 rows=215 loops=1)

  • Output: i_4."Balance", i_4."M/E date", i_4."FICOM GL", i_4."Charter Number
  • Filter: (i_4."FICOM GL" = 3300)
  • Rows Removed by Filter: 1387
  • Buffers: shared hit=12
54. 0.006 0.135 ↓ 1.1 18 1

Hash (cost=32.19..32.19 rows=17 width=25) (actual time=0.135..0.135 rows=18 loops=1)

  • Output: i_5."Balance", i_5."M/E date", i_5."FICOM GL", i_5."Charter Number
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=12
55. 0.129 0.129 ↓ 1.1 18 1

Seq Scan on public."InternalFinancialMFSROverrides" i_5 (cost=0.00..32.19 rows=17 width=25) (actual time=0.080..0.129 rows=18 loops=1)

  • Output: i_5."Balance", i_5."M/E date", i_5."FICOM GL", i_5."Charter Number
  • Filter: (i_5."FICOM GL" = 3110)
  • Rows Removed by Filter: 1584
  • Buffers: shared hit=12
56. 243.525 432.895 ↑ 1.0 487,892 1

Hash (cost=12,371.22..12,371.22 rows=490,222 width=43) (actual time=432.895..432.895 rows=487,892 loops=1)

  • Output: a.name, a.typecode, a.classcode, a.modelid, a.accountnum
  • Buckets: 524288 Batches: 2 Memory Usage: 22482kB
  • Buffers: shared hit=7469, temp written=1945
57. 189.370 189.370 ↑ 1.0 487,892 1

Seq Scan on public.empyreanaccountsetup a (cost=0.00..12,371.22 rows=490,222 width=43) (actual time=0.051..189.370 rows=487,892 loops=1)

  • Output: a.name, a.typecode, a.classcode, a.modelid, a.accountnum
  • Buffers: shared hit=7469
58. 0.010 0.728 ↑ 40.0 1 1

Hash (cost=95.52..95.52 rows=40 width=4) (actual time=0.728..0.728 rows=1 loops=1)

  • Output: m_6.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=45
59. 0.049 0.718 ↑ 40.0 1 1

Hash Join (cost=53.39..95.52 rows=40 width=4) (actual time=0.709..0.718 rows=1 loops=1)

  • Output: m_6.id
  • Hash Cond: (m_6.cp_date = (max(empyreanmodels.cp_date)))
  • Buffers: shared hit=45
60. 0.264 0.459 ↑ 1.2 119 1

Nested Loop (cost=13.43..53.24 rows=139 width=1,756) (actual time=0.186..0.459 rows=119 loops=1)

  • Output: m_6.id, m_6.cp_date, NULL::character varying(1024), NULL::character varying(255), NULL::boolean, NULL::character(1), NULL::boolean, c_1.id, NULL::character varying(15), NULL::character varying(255), NULL::character varying(31), NULL::smallint, NULL::text, NULL::integer, NULL::integer, NULL::date, (((make_date(((date_part('year'::text, (m_6.cp_date)::timestamp without time zone))::integer + CASE WHEN (date_part('month'::text, (m_6.cp_date)::timestamp without time zone) <= ((c_1.yearendmonth)::integer)::double precision) THEN 0 ELSE 1 END), 1, 1) + make_interval(0, (((c_1.yearendmonth)::integer - 11) - 1), 0, 0, 0, 0, '0'::double precision)) + '00:00:00'::interval))::date, NULL::date, (((make_date((((date_part('year'::text, (m_6.cp_date)::timestamp without time zone))::integer + CASE WHEN (date_part('month'::text, (m_6.cp_date)::timestamp without time zone) <= ((c_1.yearendmonth)::integer)::double precision) THEN 0 ELSE 1 END) - 1), 1, 1) + make_interval(0, (((c_1.yearendmonth)::integer - 11) - 1), 0, 0, 0, 0, '0'::double precision)) + '00:00:00'::interval))::date
  • Buffers: shared hit=23
61. 0.039 0.039 ↑ 1.0 1 1

Seq Scan on public.clients c_1 (cost=0.00..1.40 rows=1 width=6) (actual time=0.034..0.039 rows=1 loops=1)

  • Output: c_1.id, c_1.name, c_1.charter_id, c_1.foldername, c_1.abbreviation, c_1.reportperiod, c_1.psperiod, c_1.iscurrentclient, c_1.ispsbclient, c_1.psdbnickname, c_1.yearendmonth, c_1.massdeployment, c_1.demanddurationmethod
  • Filter: (c_1.iscurrentclient AND (c_1.id = 27))
  • Rows Removed by Filter: 31
  • Buffers: shared hit=1
62. 0.086 0.156 ↑ 1.2 119 1

Bitmap Heap Scan on public.empyreanmodels m_6 (cost=13.43..38.29 rows=139 width=12) (actual time=0.095..0.156 rows=119 loops=1)

  • Output: m_6.id, m_6.clientid, m_6.cp_date, m_6.modeldbpath, m_6.periodcode, m_6.isadhocmodel, m_6.isbudget, m_6.modelsqlserverdbname
  • Recheck Cond: (m_6.clientid = 27)
  • Filter: (NOT m_6.isadhocmodel)
  • Rows Removed by Filter: 23
  • Heap Blocks: exact=18
  • Buffers: shared hit=22
63. 0.070 0.070 ↑ 1.0 142 1

Bitmap Index Scan on empyreanmodels_uk (cost=0.00..13.39 rows=149 width=0) (actual time=0.070..0.070 rows=142 loops=1)

  • Index Cond: (m_6.clientid = 27)
  • Buffers: shared hit=4
64. 0.014 0.210 ↑ 30.0 1 1

Hash (cost=39.59..39.59 rows=30 width=8) (actual time=0.209..0.210 rows=1 loops=1)

  • Output: empyreanmodels.clientid, (max(empyreanmodels.cp_date))
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=22
65. 0.019 0.196 ↑ 30.0 1 1

GroupAggregate (cost=13.43..39.29 rows=30 width=8) (actual time=0.196..0.196 rows=1 loops=1)

  • Output: empyreanmodels.clientid, max(empyreanmodels.cp_date)
  • Group Key: empyreanmodels.clientid
  • Buffers: shared hit=22
66. 0.150 0.177 ↑ 1.2 119 1

Bitmap Heap Scan on public.empyreanmodels (cost=13.43..38.29 rows=139 width=8) (actual time=0.032..0.177 rows=119 loops=1)

  • Output: empyreanmodels.id, empyreanmodels.clientid, empyreanmodels.cp_date, empyreanmodels.modeldbpath, empyreanmodels.periodcode, empyreanmodels.isadhocmodel, empyreanmodels.isbudget, empyreanmodels.modelsqlserverdbname
  • Recheck Cond: (empyreanmodels.clientid = 27)
  • Filter: (NOT empyreanmodels.isadhocmodel)
  • Rows Removed by Filter: 23
  • Heap Blocks: exact=18
  • Buffers: shared hit=22
67. 0.027 0.027 ↑ 1.0 142 1

Bitmap Index Scan on empyreanmodels_uk (cost=0.00..13.39 rows=149 width=0) (actual time=0.027..0.027 rows=142 loops=1)

  • Index Cond: (empyreanmodels.clientid = 27)
  • Buffers: shared hit=4
Planning time : 7.900 ms
Execution time : 10,295.059 ms