explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7PpR : dashboard

Settings
# exclusive inclusive rows x rows loops node
1. 2,165.609 125,274.641 ↓ 1.7 294,411 1

Sort (cost=1,443,845.80..1,444,277.21 rows=172,565 width=208) (actual time=125,102.470..125,274.641 rows=294,411 loops=1)

  • Sort Key: ((dmv_bi_projectcostbill0.entrydate)::text) COLLATE "en_US", dmv_bi_projectcostbill0.taskid, dmv_bi_projectcostbill0.projectid, dmv_bi_projectcostbill0.userid, dmv_bi_projectcostbill0.timeoffcodeid, dmv_bi_projectcostbill0.breaktypeid, dmv_bi_p (...)
  • Sort Method: quicksort Memory: 50832kB
  • Buffers: shared hit=981818 read=116462 dirtied=18 written=5799, temp read=39451 written=107085
2. 286.146 123,109.032 ↓ 1.7 294,411 1

Subquery Scan on dmv_bi_projectcostbill0 (cost=1,422,795.65..1,428,835.42 rows=172,565 width=208) (actual time=122,394.691..123,109.032 rows=294,411 loops=1)

  • Buffers: shared hit=981805 read=116462 dirtied=18 written=5799, temp read=39451 written=107085
3. 7,957.171 122,822.886 ↓ 1.7 294,411 1

HashAggregate (cost=1,422,795.65..1,427,109.77 rows=172,565 width=184) (actual time=122,394.690..122,822.886 rows=294,411 loops=1)

  • Group Key: ((date_trunc('month'::text, pipeline2.entrydate))::date)::text, pipeline2.taskid, pipeline2.projectid, pipeline2.userid, pipeline2.timeoffcodeid, pipeline2.breaktypeid, pipeline2.locationid, pipeline2.divisionid, pipeline2.costcent (...)
  • Buffers: shared hit=981805 read=116462 dirtied=18 written=5799, temp read=39451 written=107085
4.          

CTE project_time

5. 4,227.245 5,568.532 ↓ 9.3 1,600,460 1

HashAggregate (cost=86,553.17..88,279.09 rows=172,592 width=52) (actual time=4,286.967..5,568.532 rows=1,600,460 loops=1)

  • Group Key: allocation.entrydate, allocation.userid, allocation.projectid, allocation.taskid, allocation.timeoffcodeid, allocation.breaktypeid
  • Buffers: shared read=30461 written=1233
6. 1,341.287 1,341.287 ↑ 1.0 1,725,282 1

Seq Scan on dm_projecttimeallocation_facts allocation (cost=0.00..47,720.13 rows=1,725,913 width=52) (actual time=0.891..1,341.287 rows=1,725,282 loops=1)

  • Buffers: shared read=30461 written=1233
7.          

CTE project_billing

8. 3,983.556 14,473.044 ↓ 8.3 1,600,258 1

HashAggregate (cost=120,236.02..122,647.35 rows=192,906 width=26) (actual time=13,015.243..14,473.044 rows=1,600,258 loops=1)

  • Group Key: bi.entrydate, bidm.userid, bidm.projectid, bidm.taskid
  • Buffers: shared hit=829112 read=49546 dirtied=17 written=4566
9.          

Initplan (forHashAggregate)

10. 0.466 0.466 ↑ 1.0 1 1

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.465..0.466 rows=1 loops=1)

  • Buffers: shared read=1
11. 2,145.676 10,489.022 ↓ 9.0 1,736,318 1

Hash Join (cost=59,510.22..117,341.42 rows=192,906 width=26) (actual time=7,415.092..10,489.022 rows=1,736,318 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=829112 read=49546 dirtied=17 written=4566
12. 929.822 929.822 ↓ 1.0 1,736,318 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..49,391.56 rows=1,736,156 width=28) (actual time=0.540..929.822 rows=1,736,318 loops=1)

  • Buffers: shared read=32030 written=483
13. 1,265.113 7,413.524 ↓ 9.0 1,736,318 1

Hash (cost=57,098.32..57,098.32 rows=192,952 width=30) (actual time=7,413.524..7,413.524 rows=1,736,318 loops=1)

  • Buckets: 2097152 (originally 262144) Batches: 1 (originally 1) Memory Usage: 118122kB
  • Buffers: shared hit=829112 read=17516 dirtied=17 written=4083
14. 1,262.385 6,148.411 ↓ 9.0 1,736,318 1

Nested Loop (cost=0.43..57,098.32 rows=192,952 width=30) (actual time=2.116..6,148.411 rows=1,736,318 loops=1)

  • Buffers: shared hit=829112 read=17516 dirtied=17 written=4083
15. 1.146 1.146 ↑ 1.0 7 1

Seq Scan on exchangerate (cost=0.00..1.61 rows=7 width=19) (actual time=1.127..1.146 rows=7 loops=1)

  • Filter: (variablecurrencyid = $1)
  • Rows Removed by Filter: 42
  • Buffers: shared read=2
16. 4,884.880 4,884.880 ↓ 1.3 248,045 7

Index Scan using ixbi2entrydate on billingitem2 bi (cost=0.43..6,227.15 rows=192,952 width=27) (actual time=449.813..697.840 rows=248,045 loops=7)

  • Index Cond: ((exchangerate.effectivedate <= entrydate) AND (exchangerate.enddate >= entrydate))
  • Filter: (exchangerate.fixedcurrencyid = currencyid)
  • Rows Removed by Filter: 1488273
  • Buffers: shared hit=829112 read=17514 dirtied=17 written=4083
17.          

CTE project_cost

18. 5,231.501 23,918.536 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,256.88..233,722.40 rows=1,725,646 width=130) (actual time=191.367..23,918.536 rows=1,725,284 loops=1)

  • Hash Cond: (userprojectroleratehistory.currencyid = exchangerate_1.fixedcurrencyid)
  • Join Filter: ((exchangerate_1.effectivedate <= dm_attendancetimeallocation_facts.entrydate) AND (exchangerate_1.enddate >= dm_attendancetimeallocation_facts.entrydate))
  • Buffers: shared hit=152692 read=36400 dirtied=1
19.          

Initplan (forHash Left Join)

20. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=1
21. 2,284.012 18,687.010 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,254.17..149,594.45 rows=1,725,646 width=127) (actual time=191.298..18,687.010 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.userid = userprojectroleratehistory.userid)
  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= userprojectroleratehistory.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= userprojectroleratehistory.enddate))
  • Buffers: shared hit=152690 read=36400 dirtied=1
22. 1,837.255 16,396.675 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,214.55..117,198.97 rows=1,725,646 width=120) (actual time=184.933..16,396.675 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id)
  • Buffers: shared hit=152690 read=36389 dirtied=1
23. 1,869.968 14,400.462 ↑ 1.0 1,725,284 1

Hash Left Join (cost=172.67..92,708.15 rows=1,725,646 width=116) (actual time=24.173..14,400.462 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id)
  • Buffers: shared hit=152690 read=35286 dirtied=1
24. 1,701.306 12,507.228 ↑ 1.0 1,725,284 1

Merge Left Join (cost=0.58..69,734.53 rows=1,725,646 width=112) (actual time=0.886..12,507.228 rows=1,725,284 loops=1)

  • Merge Cond: ((dm_attendancetimeallocation_facts.userid = usercostnormalizationfactor.userid) AND (dm_attendancetimeallocation_facts.entrydate = usercostnormalizationfactor.entrydate))
  • Buffers: shared hit=152690 read=35172 dirtied=1
25. 10,805.908 10,805.908 ↑ 1.0 1,725,284 1

Index Scan using ixata2userid on dm_attendancetimeallocation_facts (cost=0.43..61,081.12 rows=1,725,646 width=52) (actual time=0.044..10,805.908 rows=1,725,284 loops=1)

  • Buffers: shared hit=152690 read=35171 dirtied=1
26. 0.014 0.014 ↓ 0.0 0 1

Index Scan using uix2ucnf_useridentrydate on usercostnormalizationfactor (cost=0.15..19.85 rows=580 width=68) (actual time=0.014..0.014 rows=0 loops=1)

  • Buffers: shared read=1
27. 8.739 23.266 ↓ 1.0 2,587 1

Hash (cost=139.82..139.82 rows=2,582 width=8) (actual time=23.266..23.266 rows=2,587 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 123kB
  • Buffers: shared read=114
28. 14.527 14.527 ↓ 1.0 2,587 1

Seq Scan on project (cost=0.00..139.82 rows=2,582 width=8) (actual time=0.013..14.527 rows=2,587 loops=1)

  • Buffers: shared read=114
29. 44.048 158.958 ↓ 1.0 41,731 1

Hash (cost=1,520.28..1,520.28 rows=41,728 width=8) (actual time=158.958..158.958 rows=41,731 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1980kB
  • Buffers: shared read=1103
30. 114.910 114.910 ↓ 1.0 41,731 1

Seq Scan on task (cost=0.00..1,520.28 rows=41,728 width=8) (actual time=2.178..114.910 rows=41,731 loops=1)

  • Buffers: shared read=1103
31. 0.535 6.323 ↑ 1.0 1,272 1

Hash (cost=23.72..23.72 rows=1,272 width=19) (actual time=6.323..6.323 rows=1,272 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared read=11
32. 5.788 5.788 ↑ 1.0 1,272 1

Seq Scan on userprojectroleratehistory (cost=0.00..23.72 rows=1,272 width=19) (actual time=0.009..5.788 rows=1,272 loops=1)

  • Buffers: shared read=11
33. 0.005 0.022 ↑ 1.0 7 1

Hash (cost=1.61..1.61 rows=7 width=19) (actual time=0.022..0.022 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
34. 0.017 0.017 ↑ 1.0 7 1

Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=7 width=19) (actual time=0.010..0.017 rows=7 loops=1)

  • Filter: (variablecurrencyid = $6)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=2
35.          

CTE expense_cost

36. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.28..4.33 rows=1 width=60) (actual time=0.004..0.004 rows=0 loops=1)

  • Buffers: shared hit=1
37. 0.003 0.003 ↓ 0.0 0 1

Index Scan using ixex2approvalstatus on expense (cost=0.14..2.16 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (approvalstatus = 2)
  • Buffers: shared hit=1
38. 0.000 0.000 ↓ 0.0 0

Index Scan using ixeeexpenseid on expenseentry (cost=0.14..2.16 rows=1 width=60) (never executed)

  • Index Cond: (expenseid = expense.id)
  • Filter: (requestreimbursement AND (NOT billtoclient))
39.          

CTE pipeline1

40. 4,895.814 80,390.753 ↓ 2.0 3,446,124 1

Hash Full Join (cost=274,040.07..339,970.62 rows=1,725,646 width=188) (actual time=38,801.126..80,390.753 rows=3,446,124 loops=1)

  • Hash Cond: ((project_billing.taskid = expense_cost.taskid) AND (project_billing.projectid = expense_cost.projectid) AND (project_billing.userid = expense_cost.userid))
  • Join Filter: (expense_cost.entrydate = project_billing.entrydate)
  • Buffers: shared hit=981805 read=116407 dirtied=18 written=5799, temp read=39451 written=58813
41. 3,841.090 75,494.933 ↓ 2.0 3,446,124 1

Merge Full Join (cost=274,040.03..307,614.70 rows=1,725,646 width=136) (actual time=38,801.096..75,494.933 rows=3,446,124 loops=1)

  • Merge Cond: ((project_time.entrydate = project_cost.entrydate) AND (project_time.taskid = project_cost.taskid) AND (project_time.projectid = project_cost.projectid) AND (project_time.userid = project_cost.userid) AND (project_ti (...)
  • Buffers: shared hit=981804 read=116407 dirtied=18 written=5799, temp read=39451 written=58813
42. 4,495.072 39,822.998 ↓ 8.9 1,720,840 1

Sort (cost=60,761.36..61,243.63 rows=192,906 width=92) (actual time=38,801.091..39,822.998 rows=1,720,840 loops=1)

  • Sort Key: project_time.entrydate, project_time.taskid, project_time.projectid, project_time.userid, project_time.timeoffcodeid, project_time.breaktypeid
  • Sort Method: external sort Disk: 96720kB
  • Buffers: shared hit=829112 read=80007 dirtied=17 written=5799, temp read=31439 written=43641
43. 2,889.245 35,327.926 ↓ 8.9 1,720,840 1

Merge Full Join (cost=39,257.65..43,826.59 rows=192,906 width=92) (actual time=30,513.324..35,327.926 rows=1,720,840 loops=1)

  • Merge Cond: ((project_time.entrydate = project_billing.entrydate) AND (project_time.taskid = project_billing.taskid) AND (project_time.projectid = project_billing.projectid) AND (project_time.userid = project_billing (...)
  • Buffers: shared hit=829112 read=80007 dirtied=17 written=5799, temp read=19349 written=31551
44. 4,622.270 11,993.039 ↓ 9.3 1,600,460 1

Sort (cost=18,464.76..18,896.24 rows=172,592 width=44) (actual time=10,858.976..11,993.039 rows=1,600,460 loops=1)

  • Sort Key: project_time.entrydate, project_time.taskid, project_time.projectid, project_time.userid
  • Sort Method: external merge Disk: 53192kB
  • Buffers: shared read=30461 written=1233, temp read=6650 written=13292
45. 7,370.769 7,370.769 ↓ 9.3 1,600,460 1

CTE Scan on project_time (cost=0.00..3,451.84 rows=172,592 width=44) (actual time=4,286.970..7,370.769 rows=1,600,460 loops=1)

  • Buffers: shared read=30461 written=1233, temp written=6642
46. 4,097.735 20,445.642 ↓ 8.3 1,600,258 1

Sort (cost=20,792.89..21,275.16 rows=192,906 width=48) (actual time=19,654.342..20,445.642 rows=1,600,258 loops=1)

  • Sort Key: project_billing.entrydate, project_billing.taskid, project_billing.projectid, project_billing.userid
  • Sort Method: external sort Disk: 50800kB
  • Buffers: shared hit=829112 read=49546 dirtied=17 written=4566, temp read=12699 written=18259
47. 16,347.907 16,347.907 ↓ 8.3 1,600,258 1

CTE Scan on project_billing (cost=0.00..3,858.12 rows=192,906 width=48) (actual time=13,015.248..16,347.907 rows=1,600,258 loops=1)

  • Buffers: shared hit=829112 read=49546 dirtied=17 written=4566, temp written=5560
48. 5,204.898 31,830.845 ↑ 1.0 1,725,284 1

Sort (cost=213,278.67..217,592.79 rows=1,725,646 width=44) (actual time=30,286.579..31,830.845 rows=1,725,284 loops=1)

  • Sort Key: project_cost.entrydate, project_cost.taskid, project_cost.projectid, project_cost.userid, project_cost.timeoffcodeid, project_cost.breaktypeid
  • Sort Method: external sort Disk: 64096kB
  • Buffers: shared hit=152692 read=36400 dirtied=1, temp read=8012 written=15172
49. 26,625.947 26,625.947 ↑ 1.0 1,725,284 1

CTE Scan on project_cost (cost=0.00..34,512.92 rows=1,725,646 width=44) (actual time=191.370..26,625.947 rows=1,725,284 loops=1)

  • Buffers: shared hit=152692 read=36400 dirtied=1, temp written=7160
50. 0.001 0.006 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=52) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
51. 0.005 0.005 ↓ 0.0 0 1

CTE Scan on expense_cost (cost=0.00..0.02 rows=1 width=52) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
52.          

CTE pipeline2

53. 3,527.543 103,002.213 ↓ 2.0 3,446,124 1

Hash Left Join (cost=114.25..526,004.87 rows=1,725,646 width=184) (actual time=38,819.281..103,002.213 rows=3,446,124 loops=1)

  • Hash Cond: (pipeline1.userid = userservicecenter.userid)
  • Join Filter: ((userservicecenter.startdate <= pipeline1.entrydate) AND (userservicecenter.enddate >= pipeline1.entrydate))
  • Buffers: shared hit=981805 read=116462 dirtied=18 written=5799, temp read=39451 written=79366
54. 4,156.567 99,474.669 ↓ 2.0 3,446,124 1

Hash Left Join (cost=78.83..131,659.34 rows=1,725,646 width=168) (actual time=38,819.267..99,474.669 rows=3,446,124 loops=1)

  • Hash Cond: (pipeline1.userid = usercostcenter.userid)
  • Join Filter: ((usercostcenter.startdate <= pipeline1.entrydate) AND (usercostcenter.enddate >= pipeline1.entrydate))
  • Buffers: shared hit=981805 read=116462 dirtied=18 written=5799, temp read=39451 written=79366
55. 4,303.715 95,310.057 ↓ 2.0 3,446,124 1

Hash Left Join (cost=56.89..99,281.53 rows=1,725,646 width=152) (actual time=38,811.213..95,310.057 rows=3,446,124 loops=1)

  • Hash Cond: (pipeline1.userid = userdivision.userid)
  • Join Filter: ((userdivision.startdate <= pipeline1.entrydate) AND (userdivision.enddate >= pipeline1.entrydate))
  • Rows Removed by Join Filter: 3819
  • Buffers: shared hit=981805 read=116448 dirtied=18 written=5799, temp read=39451 written=79366
56. 4,678.061 91,001.197 ↓ 2.0 3,446,124 1

Hash Left Join (cost=30.94..66,899.73 rows=1,725,646 width=136) (actual time=38,806.060..91,001.197 rows=3,446,124 loops=1)

  • Hash Cond: (pipeline1.userid = userlocation.userid)
  • Join Filter: ((userlocation.startdate <= pipeline1.entrydate) AND (userlocation.enddate >= pipeline1.entrydate))
  • Buffers: shared hit=981805 read=116430 dirtied=18 written=5799, temp read=39451 written=79366
57. 86,318.217 86,318.217 ↓ 2.0 3,446,124 1

CTE Scan on pipeline1 (cost=0.00..34,512.92 rows=1,725,646 width=120) (actual time=38,801.129..86,318.217 rows=3,446,124 loops=1)

  • Buffers: shared hit=981805 read=116407 dirtied=18 written=5799, temp read=39451 written=79366
58. 0.249 4.919 ↑ 1.0 353 1

Hash (cost=26.53..26.53 rows=353 width=28) (actual time=4.919..4.919 rows=353 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared read=23
59. 4.670 4.670 ↑ 1.0 353 1

Seq Scan on userlocation (cost=0.00..26.53 rows=353 width=28) (actual time=2.115..4.670 rows=353 loops=1)

  • Buffers: shared read=23
60. 0.172 5.145 ↑ 1.0 353 1

Hash (cost=21.53..21.53 rows=353 width=28) (actual time=5.145..5.145 rows=353 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared read=18
61. 4.973 4.973 ↑ 1.0 353 1

Seq Scan on userdivision (cost=0.00..21.53 rows=353 width=28) (actual time=1.202..4.973 rows=353 loops=1)

  • Buffers: shared read=18
62. 0.270 8.045 ↑ 1.0 353 1

Hash (cost=17.53..17.53 rows=353 width=28) (actual time=8.045..8.045 rows=353 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared read=14
63. 7.775 7.775 ↑ 1.0 353 1

Seq Scan on usercostcenter (cost=0.00..17.53 rows=353 width=28) (actual time=2.019..7.775 rows=353 loops=1)

  • Buffers: shared read=14
64. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=21.30..21.30 rows=1,130 width=28) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
65. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on userservicecenter (cost=0.00..21.30 rows=1,130 width=28) (actual time=0.001..0.001 rows=0 loops=1)

66. 114,865.715 114,865.715 ↓ 2.0 3,446,124 1

CTE Scan on pipeline2 (cost=0.00..51,769.38 rows=1,725,646 width=184) (actual time=38,819.298..114,865.715 rows=3,446,124 loops=1)

  • Buffers: shared hit=981805 read=116462 dirtied=18 written=5799, temp read=39451 written=107085