explain.depesz.com

PostgreSQL's explain analyze made readable

Result: idkq

Settings
# exclusive inclusive rows x rows loops node
1. 1,470.179 104,220.478 ↓ 1.7 294,411 1

Sort (cost=1,431,970.85..1,432,402.26 rows=172,565 width=208) (actual time=104,120.946..104,220.478 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_projectcostbill0.locationid, dmv_bi_projectcostbill0.divisionid, dmv_bi_projectcostbill0.costcenterid, dmv_bi_projectcostbill0.servicecenterid, dmv_bi_projectcostbill0.billableamount, dmv_bi_projectcostbill0.cost, dmv_bi_projectcostbill0.expensecost, dmv_bi_projectcostbill0.allocatedhours
  • Sort Method: quicksort Memory: 50832kB
  • Buffers: shared hit=981975 read=116477 written=19051, temp read=32664 written=80936
2. 198.461 102,750.299 ↓ 1.7 294,411 1

Subquery Scan on dmv_bi_projectcostbill0 (cost=1,410,920.70..1,416,960.47 rows=172,565 width=208) (actual time=102,210.698..102,750.299 rows=294,411 loops=1)

  • Buffers: shared hit=981965 read=116474 written=19051, temp read=32664 written=80936
3. 3,974.884 102,551.838 ↓ 1.7 294,411 1

HashAggregate (cost=1,410,920.70..1,415,234.82 rows=172,565 width=184) (actual time=102,210.696..102,551.838 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.costcenterid, pipeline2.servicecenterid
  • Buffers: shared hit=981965 read=116474 written=19051, temp read=32664 written=80936
4.          

CTE project_time

5. 7,642.165 10,792.443 ↓ 9.3 1,600,460 1

HashAggregate (cost=86,553.17..88,279.09 rows=172,592 width=52) (actual time=8,087.672..10,792.443 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=5305
6. 3,150.278 3,150.278 ↑ 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=9.375..3,150.278 rows=1,725,282 loops=1)

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

CTE project_billing

8. 4,806.801 19,438.424 ↓ 8.3 1,600,258 1

HashAggregate (cost=120,236.02..122,647.35 rows=192,906 width=26) (actual time=17,770.073..19,438.424 rows=1,600,258 loops=1)

  • Group Key: bi.entrydate, bidm.userid, bidm.projectid, bidm.taskid
  • Buffers: shared hit=829141 read=49548 written=1829
9.          

Initplan (forHashAggregate)

10. 0.464 0.464 ↑ 1.0 1 1

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

  • Buffers: shared read=1
11. 2,669.468 14,631.159 ↓ 9.0 1,736,318 1

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

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=829141 read=49548 written=1829
12. 1,224.219 1,224.219 ↓ 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.537..1,224.219 rows=1,736,318 loops=1)

  • Buffers: shared read=32030 written=408
13. 1,776.826 10,737.472 ↓ 9.0 1,736,318 1

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

  • Buckets: 2097152 (originally 262144) Batches: 1 (originally 1) Memory Usage: 118122kB
  • Buffers: shared hit=829141 read=17518 written=1421
14. 1,682.545 8,960.646 ↓ 9.0 1,736,318 1

Nested Loop (cost=0.43..57,098.32 rows=192,952 width=30) (actual time=2.014..8,960.646 rows=1,736,318 loops=1)

  • Buffers: shared hit=829141 read=17518 written=1421
15. 0.978 0.978 ↑ 1.0 7 1

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

  • Filter: (variablecurrencyid = $1)
  • Rows Removed by Filter: 42
  • Buffers: shared read=2
16. 7,277.123 7,277.123 ↓ 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=707.615..1,039.589 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=829141 read=17516 written=1421
17.          

CTE project_cost

18. 3,422.090 12,380.451 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,256.88..233,722.40 rows=1,725,646 width=130) (actual time=50.052..12,380.451 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=152823 read=36410 written=11917
19.          

Initplan (forHash Left Join)

20. 0.013 0.013 ↑ 1.0 1 1

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

  • Buffers: shared read=1
21. 1,555.341 8,958.306 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,254.17..149,594.45 rows=1,725,646 width=127) (actual time=49.978..8,958.306 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=152823 read=36408 written=11917
22. 1,238.580 7,401.038 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,214.55..117,198.97 rows=1,725,646 width=120) (actual time=48.034..7,401.038 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id)
  • Buffers: shared hit=152823 read=36397 written=11917
23. 1,231.957 6,119.817 ↑ 1.0 1,725,284 1

Hash Left Join (cost=172.67..92,708.15 rows=1,725,646 width=116) (actual time=5.312..6,119.817 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id)
  • Buffers: shared hit=152823 read=35294 written=11917
24. 1,149.387 4,883.794 ↑ 1.0 1,725,284 1

Merge Left Join (cost=0.58..69,734.53 rows=1,725,646 width=112) (actual time=1.221..4,883.794 rows=1,725,284 loops=1)

  • Merge Cond: ((dm_attendancetimeallocation_facts.userid = usercostnormalizationfactor.userid) AND (dm_attendancetimeallocation_facts.entrydate = usercostnormalizationfactor.entrydate))
  • Buffers: shared hit=152823 read=35180 written=11917
25. 3,734.156 3,734.156 ↑ 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.966..3,734.156 rows=1,725,284 loops=1)

  • Buffers: shared hit=152823 read=35179 written=11917
26. 0.251 0.251 ↓ 0.0 0 1

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

  • Buffers: shared read=1
27. 0.846 4.066 ↓ 1.0 2,587 1

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

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

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

  • Buffers: shared read=114
29. 13.206 42.641 ↓ 1.0 41,731 1

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

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

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

  • Buffers: shared read=1103
31. 0.463 1.927 ↑ 1.0 1,272 1

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

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

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

  • Buffers: shared read=11
33. 0.007 0.042 ↑ 1.0 7 1

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

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

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

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

CTE expense_cost

36. 0.002 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.28..4.33 rows=1 width=60) (actual time=0.005..0.005 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. 2,837.243 70,865.672 ↓ 2.0 3,446,124 1

Hash Full Join (cost=274,040.07..339,970.62 rows=1,725,646 width=188) (actual time=48,714.281..70,865.672 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=981965 read=116419 written=19051, temp read=12090 written=12090
41. 2,311.212 68,028.422 ↓ 2.0 3,446,124 1

Merge Full Join (cost=274,040.03..307,614.70 rows=1,725,646 width=136) (actual time=48,714.244..68,028.422 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_time.timeoffcodeid = project_cost.timeoffcodeid) AND (project_time.breaktypeid = project_cost.breaktypeid))
  • Buffers: shared hit=981964 read=116419 written=19051, temp read=12090 written=12090
42. 3,254.021 49,250.149 ↓ 8.9 1,720,840 1

Sort (cost=60,761.36..61,243.63 rows=192,906 width=92) (actual time=48,714.239..49,250.149 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=829141 read=80009 written=7134, temp read=12090 written=12090
43. 2,386.834 45,996.128 ↓ 8.9 1,720,840 1

Merge Full Join (cost=39,257.65..43,826.59 rows=192,906 width=92) (actual time=42,115.916..45,996.128 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.userid))
  • Buffers: shared hit=829141 read=80009 written=7134
44. 5,031.876 19,302.927 ↓ 9.3 1,600,460 1

Sort (cost=18,464.76..18,896.24 rows=172,592 width=44) (actual time=18,553.347..19,302.927 rows=1,600,460 loops=1)

  • Sort Key: project_time.entrydate, project_time.taskid, project_time.projectid, project_time.userid
  • Sort Method: quicksort Memory: 174188kB
  • Buffers: shared read=30461 written=5305
45. 14,271.051 14,271.051 ↓ 9.3 1,600,460 1

CTE Scan on project_time (cost=0.00..3,451.84 rows=172,592 width=44) (actual time=8,087.676..14,271.051 rows=1,600,460 loops=1)

  • Buffers: shared read=30461 written=5305
46. 3,007.055 24,306.367 ↓ 8.3 1,600,258 1

Sort (cost=20,792.89..21,275.16 rows=192,906 width=48) (actual time=23,562.560..24,306.367 rows=1,600,258 loops=1)

  • Sort Key: project_billing.entrydate, project_billing.taskid, project_billing.projectid, project_billing.userid
  • Sort Method: quicksort Memory: 170411kB
  • Buffers: shared hit=829141 read=49548 written=1829
47. 21,299.312 21,299.312 ↓ 8.3 1,600,258 1

CTE Scan on project_billing (cost=0.00..3,858.12 rows=192,906 width=48) (actual time=17,770.077..21,299.312 rows=1,600,258 loops=1)

  • Buffers: shared hit=829141 read=49548 written=1829
48. 2,511.100 16,467.061 ↑ 1.0 1,725,284 1

Sort (cost=213,278.67..217,592.79 rows=1,725,646 width=44) (actual time=15,650.075..16,467.061 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: quicksort Memory: 183940kB
  • Buffers: shared hit=152823 read=36410 written=11917
49. 13,955.961 13,955.961 ↑ 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=50.055..13,955.961 rows=1,725,284 loops=1)

  • Buffers: shared hit=152823 read=36410 written=11917
50. 0.002 0.007 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=52) (actual time=0.007..0.007 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. 2,259.514 92,239.953 ↓ 2.0 3,446,124 1

Hash Left Join (cost=213,436.10..514,129.92 rows=1,725,646 width=184) (actual time=81,349.751..92,239.953 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=981965 read=116474 written=19051, temp read=32664 written=53217
54. 2,196.237 89,952.727 ↓ 2.0 3,446,124 1

Hash Left Join (cost=213,414.16..481,752.12 rows=1,725,646 width=168) (actual time=81,322.026..89,952.727 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=981965 read=116460 written=19051, temp read=32664 written=53217
55. 2,192.707 87,727.706 ↓ 2.0 3,446,124 1

Hash Left Join (cost=213,388.22..449,370.31 rows=1,725,646 width=152) (actual time=81,293.230..87,727.706 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=981965 read=116442 written=19051, temp read=32664 written=53217
56. 2,022.869 85,515.059 ↓ 2.0 3,446,124 1

Merge Left Join (cost=213,357.28..416,983.51 rows=1,725,646 width=136) (actual time=81,273.271..85,515.059 rows=3,446,124 loops=1)

  • Merge Cond: (pipeline1.userid = userservicecenter.userid)
  • Join Filter: ((userservicecenter.startdate <= pipeline1.entrydate) AND (userservicecenter.enddate >= pipeline1.entrydate))
  • Buffers: shared hit=981965 read=116419 written=19051, temp read=32664 written=53217
57. 9,289.211 83,492.171 ↓ 2.0 3,446,124 1

Sort (cost=213,278.67..217,592.79 rows=1,725,646 width=120) (actual time=81,273.246..83,492.171 rows=3,446,124 loops=1)

  • Sort Key: pipeline1.userid
  • Sort Method: external merge Disk: 164584kB
  • Buffers: shared hit=981965 read=116419 written=19051, temp read=32664 written=53217
58. 74,202.960 74,202.960 ↓ 2.0 3,446,124 1

CTE Scan on pipeline1 (cost=0.00..34,512.92 rows=1,725,646 width=120) (actual time=48,714.284..74,202.960 rows=3,446,124 loops=1)

  • Buffers: shared hit=981965 read=116419 written=19051, temp read=12090 written=32643
59. 0.017 0.019 ↓ 0.0 0 1

Sort (cost=78.60..81.43 rows=1,130 width=28) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: userservicecenter.userid
  • Sort Method: quicksort Memory: 25kB
60. 0.002 0.002 ↓ 0.0 0 1

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

61. 0.140 19.940 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=23
63. 0.128 28.784 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=18
65. 0.128 27.712 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=14
67. 98,576.954 98,576.954 ↓ 2.0 3,446,124 1

CTE Scan on pipeline2 (cost=0.00..51,769.38 rows=1,725,646 width=184) (actual time=81,350.577..98,576.954 rows=3,446,124 loops=1)

  • Buffers: shared hit=981965 read=116474 written=19051, temp read=32664 written=80936