explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m2NI : AllstateG3trial_after 2 index

Settings
# exclusive inclusive rows x rows loops node
1. 1,357.889 98,963.593 ↓ 1.7 294,411 1

Sort (cost=1,442,734.39..1,443,165.76 rows=172,547 width=208) (actual time=98,848.352..98,963.593 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=256130 read=116437 written=411, temp read=39451 written=107085
2. 154.016 97,605.704 ↓ 1.7 294,411 1

Subquery Scan on dmv_bi_projectcostbill0 (cost=1,421,686.56..1,427,725.71 rows=172,547 width=208) (actual time=97,180.740..97,605.704 rows=294,411 loops=1)

  • Buffers: shared hit=256130 read=116437 written=411, temp read=39451 written=107085
3. 5,187.646 97,451.688 ↓ 1.7 294,411 1

HashAggregate (cost=1,421,686.56..1,426,000.24 rows=172,547 width=184) (actual time=97,180.738..97,451.688 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=256130 read=116437 written=411, temp read=39451 written=107085
4.          

CTE project_time

5. 5,351.261 6,881.796 ↓ 9.3 1,600,460 1

HashAggregate (cost=86,526.39..88,251.48 rows=172,509 width=52) (actual time=5,068.285..6,881.796 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=411
6. 1,530.535 1,530.535 ↓ 1.0 1,725,282 1

Seq Scan on dm_projecttimeallocation_facts allocation (cost=0.00..47,711.89 rows=1,725,089 width=52) (actual time=0.012..1,530.535 rows=1,725,282 loops=1)

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

CTE project_billing

8. 4,807.937 11,709.092 ↓ 8.3 1,600,258 1

HashAggregate (cost=119,313.38..121,724.77 rows=192,911 width=26) (actual time=9,757.466..11,709.092 rows=1,600,258 loops=1)

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

Initplan (forHashAggregate)

10. 0.033 0.033 ↑ 1.0 1 1

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

  • Buffers: shared read=1
11. 2,341.400 6,901.122 ↓ 9.0 1,736,318 1

Hash Join (cost=58,586.80..116,418.70 rows=192,911 width=26) (actual time=3,669.200..6,901.122 rows=1,736,318 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=103446 read=49545
12. 890.808 890.808 ↓ 1.0 1,736,318 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..49,392.03 rows=1,736,203 width=28) (actual time=0.012..890.808 rows=1,736,318 loops=1)

  • Buffers: shared hit=2 read=32028
13. 1,181.990 3,668.914 ↓ 9.0 1,736,318 1

Hash (cost=56,175.25..56,175.25 rows=192,924 width=30) (actual time=3,668.914..3,668.914 rows=1,736,318 loops=1)

  • Buckets: 2097152 (originally 262144) Batches: 1 (originally 1) Memory Usage: 118122kB
  • Buffers: shared hit=103444 read=17517
14. 1,319.731 2,486.924 ↓ 9.0 1,736,318 1

Nested Loop (cost=0.43..56,175.25 rows=192,924 width=30) (actual time=0.095..2,486.924 rows=1,736,318 loops=1)

  • Buffers: shared hit=103444 read=17517
15. 0.055 0.055 ↑ 1.0 7 1

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

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

Index Scan using dbatest1 on billingitem2 bi (cost=0.43..6,095.57 rows=192,924 width=27) (actual time=0.009..166.734 rows=248,045 loops=7)

  • Index Cond: ((currencyid = exchangerate.fixedcurrencyid) AND (exchangerate.effectivedate <= entrydate) AND (exchangerate.enddate >= entrydate))
  • Buffers: shared hit=103444 read=17515
17.          

CTE project_cost

18. 3,902.875 11,076.930 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,257.06..233,679.34 rows=1,725,468 width=130) (actual time=54.172..11,076.930 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=152683 read=36389
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.002..0.003 rows=1 loops=1)

  • Buffers: shared hit=1
21. 1,719.344 7,174.034 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,254.35..149,560.07 rows=1,725,468 width=127) (actual time=54.126..7,174.034 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=152681 read=36389
22. 1,417.909 5,453.618 ↑ 1.0 1,725,284 1

Hash Left Join (cost=2,214.73..117,167.92 rows=1,725,468 width=120) (actual time=53.036..5,453.618 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id)
  • Buffers: shared hit=152679 read=36380
23. 1,416.891 3,985.854 ↑ 1.0 1,725,284 1

Hash Left Join (cost=172.78..92,708.12 rows=1,725,468 width=116) (actual time=3.096..3,985.854 rows=1,725,284 loops=1)

  • Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id)
  • Buffers: shared hit=152677 read=35279
24. 1,263.459 2,565.899 ↑ 1.0 1,725,284 1

Merge Left Join (cost=0.58..69,730.98 rows=1,725,468 width=112) (actual time=0.018..2,565.899 rows=1,725,284 loops=1)

  • Merge Cond: ((dm_attendancetimeallocation_facts.userid = usercostnormalizationfactor.userid) AND (dm_attendancetimeallocation_facts.entrydate = usercostnormalizationfactor.entrydate))
  • Buffers: shared hit=152675 read=35167
25. 1,302.438 1,302.438 ↑ 1.0 1,725,284 1

Index Scan using ixata2userid on dm_attendancetimeallocation_facts (cost=0.43..61,078.45 rows=1,725,468 width=52) (actual time=0.014..1,302.438 rows=1,725,284 loops=1)

  • Buffers: shared hit=152674 read=35167
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. 1.211 3.064 ↑ 1.0 2,587 1

Hash (cost=139.87..139.87 rows=2,587 width=8) (actual time=3.064..3.064 rows=2,587 loops=1)

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

Seq Scan on project (cost=0.00..139.87 rows=2,587 width=8) (actual time=0.002..1.853 rows=2,587 loops=1)

  • Buffers: shared hit=2 read=112
29. 18.883 49.855 ↑ 1.0 41,731 1

Hash (cost=1,520.31..1,520.31 rows=41,731 width=8) (actual time=49.855..49.855 rows=41,731 loops=1)

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

Seq Scan on task (cost=0.00..1,520.31 rows=41,731 width=8) (actual time=0.025..30.972 rows=41,731 loops=1)

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

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

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

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

  • Buffers: shared hit=2 read=9
33. 0.006 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.012 0.012 ↑ 1.0 7 1

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

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

CTE expense_cost

36. 0.002 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.002 0.002 ↓ 0.0 0 1

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

Hash Full Join (cost=274,007.38..339,931.56 rows=1,725,468 width=188) (actual time=45,326.206..69,392.084 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=256130 read=116395 written=411, temp read=39451 written=58813
41. 2,766.275 65,881.398 ↓ 2.0 3,446,124 1

Merge Full Join (cost=274,007.35..307,578.99 rows=1,725,468 width=136) (actual time=45,326.184..65,881.398 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=256129 read=116395 written=411, temp read=39451 written=58813
42. 3,824.523 46,187.550 ↓ 8.9 1,720,840 1

Sort (cost=60,751.96..61,234.23 rows=192,911 width=92) (actual time=45,326.179..46,187.550 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=103446 read=80006 written=411, temp read=31439 written=43641
43. 2,572.727 42,363.027 ↓ 8.9 1,720,840 1

Merge Full Join (cost=39,248.75..43,816.71 rows=192,911 width=92) (actual time=37,999.267..42,363.027 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=103446 read=80006 written=411, temp read=19349 written=31551
44. 6,098.792 15,681.419 ↓ 9.3 1,600,460 1

Sort (cost=18,455.28..18,886.55 rows=172,509 width=44) (actual time=14,611.523..15,681.419 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=411, temp read=6650 written=13292
45. 9,582.627 9,582.627 ↓ 9.3 1,600,460 1

CTE Scan on project_time (cost=0.00..3,450.18 rows=172,509 width=44) (actual time=5,068.289..9,582.627 rows=1,600,460 loops=1)

  • Buffers: shared read=30461 written=411, temp written=6642
46. 9,349.315 24,108.881 ↓ 8.3 1,600,258 1

Sort (cost=20,793.47..21,275.75 rows=192,911 width=48) (actual time=23,387.738..24,108.881 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=103446 read=49545, temp read=12699 written=18259
47. 14,759.566 14,759.566 ↓ 8.3 1,600,258 1

CTE Scan on project_billing (cost=0.00..3,858.22 rows=192,911 width=48) (actual time=9,757.470..14,759.566 rows=1,600,258 loops=1)

  • Buffers: shared hit=103446 read=49545, temp written=5560
48. 3,865.327 16,927.573 ↑ 1.0 1,725,284 1

Sort (cost=213,255.39..217,569.06 rows=1,725,468 width=44) (actual time=16,160.005..16,927.573 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=152683 read=36389, temp read=8012 written=15172
49. 13,062.246 13,062.246 ↑ 1.0 1,725,284 1

CTE Scan on project_cost (cost=0.00..34,509.36 rows=1,725,468 width=44) (actual time=54.175..13,062.246 rows=1,725,284 loops=1)

  • Buffers: shared hit=152683 read=36389, temp written=7160
50. 0.001 0.005 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=1
52.          

CTE pipeline2

53. 2,404.332 84,426.218 ↓ 2.0 3,446,124 1

Hash Left Join (cost=103.29..525,939.66 rows=1,725,468 width=184) (actual time=45,327.770..84,426.218 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=256130 read=116437 written=411, temp read=39451 written=79366
54. 2,866.319 82,021.885 ↓ 2.0 3,446,124 1

Hash Left Join (cost=67.87..131,634.80 rows=1,725,468 width=168) (actual time=45,327.759..82,021.885 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=256130 read=116437 written=411, temp read=39451 written=79366
55. 2,871.704 79,155.081 ↓ 2.0 3,446,124 1

Hash Left Join (cost=45.92..99,260.33 rows=1,725,468 width=152) (actual time=45,327.267..79,155.081 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=256130 read=116423 written=411, temp read=39451 written=79366
56. 3,200.197 76,282.913 ↓ 2.0 3,446,124 1

Hash Left Join (cost=30.94..66,892.83 rows=1,725,468 width=136) (actual time=45,326.794..76,282.913 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=256130 read=116418 written=411, temp read=39451 written=79366
57. 73,082.143 73,082.143 ↓ 2.0 3,446,124 1

CTE Scan on pipeline1 (cost=0.00..34,509.36 rows=1,725,468 width=120) (actual time=45,326.210..73,082.143 rows=3,446,124 loops=1)

  • Buffers: shared hit=256130 read=116395 written=411, temp read=39451 written=79366
58. 0.199 0.573 ↑ 1.0 353 1

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

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

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

  • Buffers: shared read=23
60. 0.241 0.464 ↑ 1.0 353 1

Hash (cost=10.57..10.57 rows=353 width=28) (actual time=0.464..0.464 rows=353 loops=1)

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

Index Only Scan using ix4ud_userdivisionstartend on userdivision (cost=0.27..10.57 rows=353 width=28) (actual time=0.034..0.223 rows=353 loops=1)

  • Heap Fetches: 0
  • Buffers: shared read=5
62. 0.231 0.485 ↑ 1.0 353 1

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

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

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

CTE Scan on pipeline2 (cost=0.00..51,764.04 rows=1,725,468 width=184) (actual time=45,327.782..92,264.042 rows=3,446,124 loops=1)

  • Buffers: shared hit=256130 read=116437 written=411, temp read=39451 written=107085