explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D6q9 : dashboard1

Settings
# exclusive inclusive rows x rows loops node
1. 1,370.374 75,404.845 ↓ 1.7 294,411 1

Sort (cost=1,443,511.83..1,443,943.14 rows=172,527 width=208) (actual time=75,283.238..75,404.845 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=981795 read=116446, temp read=39451 written=107085
2. 152.959 74,034.471 ↓ 1.7 294,411 1

Subquery Scan on dmv_bi_projectcostbill0 (cost=1,422,466.58..1,428,505.03 rows=172,527 width=208) (actual time=73,608.253..74,034.471 rows=294,411 loops=1)

  • Buffers: shared hit=981776 read=116446, temp read=39451 written=107085
3. 4,483.367 73,881.512 ↓ 1.7 294,411 1

HashAggregate (cost=1,422,466.58..1,426,779.76 rows=172,527 width=184) (actual time=73,608.251..73,881.512 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=981776 read=116446, temp read=39451 written=107085
4.          

CTE project_time

5. 3,194.702 3,904.193 ↓ 9.3 1,600,460 1

HashAggregate (cost=86,533.25..88,258.55 rows=172,530 width=52) (actual time=2,919.545..3,904.193 rows=1,600,460 loops=1)

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

Seq Scan on dm_projecttimeallocation_facts allocation (cost=0.00..47,714.00 rows=1,725,300 width=52) (actual time=0.010..709.491 rows=1,725,282 loops=1)

  • Buffers: shared read=30461
7.          

CTE project_billing

8. 3,291.121 10,372.998 ↓ 8.3 1,600,258 1

HashAggregate (cost=120,207.99..122,619.42 rows=192,914 width=26) (actual time=9,201.445..10,372.998 rows=1,600,258 loops=1)

  • Group Key: bi.entrydate, bidm.userid, bidm.projectid, bidm.taskid
  • Buffers: shared hit=829089 read=49544
9.          

Initplan (forHashAggregate)

10. 0.465 0.465 ↑ 1.0 1 1

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

  • Buffers: shared read=1
11. 1,841.799 7,081.412 ↓ 9.0 1,736,318 1

Hash Join (cost=59,480.98..117,313.27 rows=192,914 width=26) (actual time=4,554.531..7,081.412 rows=1,736,318 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=829089 read=49544
12. 685.393 685.393 ↓ 1.0 1,736,318 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..49,392.29 rows=1,736,229 width=28) (actual time=0.013..685.393 rows=1,736,318 loops=1)

  • Buffers: shared hit=2 read=32028
13. 807.299 4,554.220 ↓ 9.0 1,736,318 1

Hash (cost=57,069.43..57,069.43 rows=192,924 width=30) (actual time=4,554.220..4,554.220 rows=1,736,318 loops=1)

  • Buckets: 2097152 (originally 262144) Batches: 1 (originally 1) Memory Usage: 118122kB
  • Buffers: shared hit=829087 read=17516
14. 842.347 3,746.921 ↓ 9.0 1,736,318 1

Nested Loop (cost=0.43..57,069.43 rows=192,924 width=30) (actual time=0.982..3,746.921 rows=1,736,318 loops=1)

  • Buffers: shared hit=829087 read=17516
15. 0.946 0.946 ↑ 1.0 7 1

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

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

Index Scan using ixbi2entrydate on billingitem2 bi (cost=0.43..6,223.31 rows=192,924 width=27) (actual time=294.104..414.804 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=829087 read=17514
17.          

CTE project_cost

18. 3,382.632 9,624.135 ↓ 1.0 1,725,284 1

Hash Left Join (cost=2,256.88..233,650.66 rows=1,725,268 width=130) (actual time=55.916..9,624.135 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=152686 read=36386
19.          

Initplan (forHash Left Join)

20. 0.004 0.004 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
21. 1,514.494 6,241.481 ↓ 1.0 1,725,284 1

Hash Left Join (cost=2,254.17..149,541.14 rows=1,725,268 width=127) (actual time=55.867..6,241.481 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=152684 read=36386
22. 1,227.563 4,725.762 ↓ 1.0 1,725,284 1

Hash Left Join (cost=2,214.55..117,152.74 rows=1,725,268 width=120) (actual time=54.627..4,725.762 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id)
  • Buffers: shared hit=152682 read=36377
23. 1,217.395 3,452.666 ↓ 1.0 1,725,284 1

Hash Left Join (cost=172.67..92,681.23 rows=1,725,268 width=116) (actual time=8.992..3,452.666 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id)
  • Buffers: shared hit=152680 read=35276
24. 1,105.062 2,226.312 ↓ 1.0 1,725,284 1

Merge Left Join (cost=0.58..69,726.97 rows=1,725,268 width=112) (actual time=0.017..2,226.312 rows=1,725,284 loops=1)

  • Merge Cond: ((dm_attendancetimeallocation_facts.userid = usercostnormalizationfactor.userid) AND (dm_attendancetimeallocation_facts.entrydate = usercostnormalizationfactor.entrydate))
  • Buffers: shared hit=152678 read=35164
25. 1,121.248 1,121.248 ↓ 1.0 1,725,284 1

Index Scan using ixata2userid on dm_attendancetimeallocation_facts (cost=0.43..61,075.45 rows=1,725,268 width=52) (actual time=0.013..1,121.248 rows=1,725,284 loops=1)

  • Buffers: shared hit=152677 read=35164
26. 0.002 0.002 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
27. 0.989 8.959 ↓ 1.0 2,587 1

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

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

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

  • Buffers: shared hit=2 read=112
29. 15.464 45.533 ↓ 1.0 41,731 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 1980kB
  • Buffers: shared hit=2 read=1101
30. 30.069 30.069 ↓ 1.0 41,731 1

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

  • Buffers: shared hit=2 read=1101
31. 0.447 1.225 ↑ 1.0 1,272 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared hit=2 read=9
32. 0.778 0.778 ↑ 1.0 1,272 1

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

  • Buffers: shared hit=2 read=9
33. 0.002 0.018 ↑ 1.0 7 1

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

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

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

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

CTE expense_cost

36. 0.002 0.003 ↓ 0.0 0 1

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

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

Index Scan using ixex2approvalstatus on expense (cost=0.14..2.16 rows=1 width=8) (actual time=0.001..0.001 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. 3,080.805 49,373.499 ↓ 2.0 3,446,124 1

Hash Full Join (cost=273,984.55..339,901.53 rows=1,725,268 width=188) (actual time=28,382.387..49,373.499 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=981776 read=116391, temp read=39451 written=58813
41. 2,416.180 46,292.691 ↓ 2.0 3,446,124 1

Merge Full Join (cost=273,984.51..307,552.71 rows=1,725,268 width=136) (actual time=28,382.364..46,292.691 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=981775 read=116391, temp read=39451 written=58813
42. 3,168.607 29,113.940 ↓ 8.9 1,720,840 1

Sort (cost=60,755.29..61,237.57 rows=192,914 width=92) (actual time=28,382.360..29,113.940 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=829089 read=80005, temp read=31439 written=43641
43. 1,928.125 25,945.333 ↓ 8.9 1,720,840 1

Merge Full Join (cost=39,251.49..43,819.75 rows=192,914 width=92) (actual time=22,622.362..25,945.333 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=829089 read=80005, temp read=19349 written=31551
44. 3,439.010 8,649.493 ↓ 9.3 1,600,460 1

Sort (cost=18,457.68..18,889.01 rows=172,530 width=44) (actual time=7,788.690..8,649.493 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, temp read=6650 written=13292
45. 5,210.483 5,210.483 ↓ 9.3 1,600,460 1

CTE Scan on project_time (cost=0.00..3,450.60 rows=172,530 width=44) (actual time=2,919.547..5,210.483 rows=1,600,460 loops=1)

  • Buffers: shared read=30461, temp written=6642
46. 3,598.415 15,367.715 ↓ 8.3 1,600,258 1

Sort (cost=20,793.81..21,276.10 rows=192,914 width=48) (actual time=14,833.663..15,367.715 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=829089 read=49544, temp read=12699 written=18259
47. 11,769.300 11,769.300 ↓ 8.3 1,600,258 1

CTE Scan on project_billing (cost=0.00..3,858.28 rows=192,914 width=48) (actual time=9,201.448..11,769.300 rows=1,600,258 loops=1)

  • Buffers: shared hit=829089 read=49544, temp written=5560
48. 3,333.069 14,762.571 ↓ 1.0 1,725,284 1

Sort (cost=213,229.23..217,542.40 rows=1,725,268 width=44) (actual time=14,041.119..14,762.571 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=152686 read=36386, temp read=8012 written=15172
49. 11,429.502 11,429.502 ↓ 1.0 1,725,284 1

CTE Scan on project_cost (cost=0.00..34,505.36 rows=1,725,268 width=44) (actual time=55.918..11,429.502 rows=1,725,284 loops=1)

  • Buffers: shared hit=152686 read=36386, temp written=7160
50. 0.000 0.003 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=1
52.          

CTE pipeline2

53. 2,145.554 62,649.282 ↓ 2.0 3,446,124 1

Hash Left Join (cost=114.25..525,889.67 rows=1,725,268 width=184) (actual time=28,387.814..62,649.282 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=981776 read=116446, temp read=39451 written=79366
54. 2,517.327 60,503.727 ↓ 2.0 3,446,124 1

Hash Left Join (cost=78.83..131,630.51 rows=1,725,268 width=168) (actual time=28,387.803..60,503.727 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=981776 read=116446, temp read=39451 written=79366
55. 2,550.751 57,984.832 ↓ 2.0 3,446,124 1

Hash Left Join (cost=56.89..99,259.79 rows=1,725,268 width=152) (actual time=28,386.229..57,984.832 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=981776 read=116432, temp read=39451 written=79366
56. 2,795.211 55,432.089 ↓ 2.0 3,446,124 1

Hash Left Join (cost=30.94..66,885.08 rows=1,725,268 width=136) (actual time=28,384.229..55,432.089 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=981776 read=116414, temp read=39451 written=79366
57. 52,635.052 52,635.052 ↓ 2.0 3,446,124 1

CTE Scan on pipeline1 (cost=0.00..34,505.36 rows=1,725,268 width=120) (actual time=28,382.390..52,635.052 rows=3,446,124 loops=1)

  • Buffers: shared hit=981776 read=116391, temp read=39451 written=79366
58. 0.124 1.826 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=23
60. 0.126 1.992 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=18
62. 0.125 1.568 ↑ 1.0 353 1

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

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

Seq Scan on usercostcenter (cost=0.00..17.53 rows=353 width=28) (actual time=0.765..1.443 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. 69,398.145 69,398.145 ↓ 2.0 3,446,124 1

CTE Scan on pipeline2 (cost=0.00..51,758.04 rows=1,725,268 width=184) (actual time=28,387.829..69,398.145 rows=3,446,124 loops=1)

  • Buffers: shared hit=981776 read=116446, temp read=39451 written=107085