#	exclusive	inclusive	rows x	rows	loops	node
1.	1,783.227	103,783.510	↓ 1.7	294,411	1	→ Sort (cost=1,442,734.39..1,443,165.76 rows=172,547 width=208) (actual time=103,670.463..103,783.510 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=256130 read=116437, temp read=39451 written=107085
2.	323.802	102,000.283	↓ 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=101,170.265..102,000.283 rows=294,411 loops=1) Buffers: shared hit=256130 read=116437, temp read=39451 written=107085
3.	7,020.185	101,676.481	↓ 1.7	294,411	1	→ HashAggregate (cost=1,421,686.56..1,426,000.24 rows=172,547 width=184) (actual time=101,170.263..101,676.481 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=256130 read=116437, temp read=39451 written=107085
4.						CTE project_time
5.	3,982.166	4,800.329	↓ 9.3	1,600,460	1	→ HashAggregate (cost=86,526.39..88,251.48 rows=172,509 width=52) (actual time=3,292.583..4,800.329 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.	818.163	818.163	↓ 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.013..818.163 rows=1,725,282 loops=1) Buffers: shared read=30461
7.						CTE project_billing
8.	4,627.499	10,808.448	↓ 8.3	1,600,258	1	→ HashAggregate (cost=119,313.38..121,724.77 rows=192,911 width=26) (actual time=9,081.329..10,808.448 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.009	0.009	↑ 1.0	1	1	→ Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1) Buffers: shared read=1
11.	2,344.720	6,180.940	↓ 9.0	1,736,318	1	→ Hash Join (cost=58,586.80..116,418.70 rows=192,911 width=26) (actual time=2,937.580..6,180.940 rows=1,736,318 loops=1) Hash Cond: (bidm.billingitemid = bi.id) Buffers: shared hit=103446 read=49545
12.	898.956	898.956	↓ 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.013..898.956 rows=1,736,318 loops=1) Buffers: shared hit=2 read=32028
13.	979.390	2,937.264	↓ 9.0	1,736,318	1	→ Hash (cost=56,175.25..56,175.25 rows=192,924 width=30) (actual time=2,937.264..2,937.264 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,043.089	1,957.874	↓ 9.0	1,736,318	1	→ Nested Loop (cost=0.43..56,175.25 rows=192,924 width=30) (actual time=0.074..1,957.874 rows=1,736,318 loops=1) Buffers: shared hit=103444 read=17517
15.	0.032	0.032	↑ 1.0	7	1	→ Seq Scan on exchangerate (cost=0.00..1.61 rows=7 width=19) (actual time=0.023..0.032 rows=7 loops=1) Filter: (variablecurrencyid = $1) Rows Removed by Filter: 42 Buffers: shared read=2
16.	914.753	914.753	↓ 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..130.679 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.	4,502.649	12,703.399	↑ 1.0	1,725,284	1	→ Hash Left Join (cost=2,257.06..233,679.34 rows=1,725,468 width=130) (actual time=93.340..12,703.399 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.003..0.003 rows=1 loops=1) Buffers: shared hit=1
21.	1,934.121	8,200.717	↑ 1.0	1,725,284	1	→ Hash Left Join (cost=2,254.35..149,560.07 rows=1,725,468 width=127) (actual time=93.270..8,200.717 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,600.060	6,265.137	↑ 1.0	1,725,284	1	→ Hash Left Join (cost=2,214.73..117,167.92 rows=1,725,468 width=120) (actual time=91.789..6,265.137 rows=1,725,284 loops=1) Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id) Buffers: shared hit=152679 read=36380
23.	1,618.218	4,576.306	↑ 1.0	1,725,284	1	→ Hash Left Join (cost=172.78..92,708.12 rows=1,725,468 width=116) (actual time=2.932..4,576.306 rows=1,725,284 loops=1) Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id) Buffers: shared hit=152677 read=35279
24.	1,470.846	2,955.193	↑ 1.0	1,725,284	1	→ Merge Left Join (cost=0.58..69,730.98 rows=1,725,468 width=112) (actual time=0.021..2,955.193 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,484.344	1,484.344	↑ 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.013..1,484.344 rows=1,725,284 loops=1) Buffers: shared hit=152674 read=35167
26.	0.003	0.003	↓ 0.0	0	1	→ Index Scan using uix2ucnf_useridentrydate on usercostnormalizationfactor (cost=0.15..19.85 rows=580 width=68) (actual time=0.003..0.003 rows=0 loops=1) Buffers: shared hit=1
27.	0.963	2.895	↑ 1.0	2,587	1	→ Hash (cost=139.87..139.87 rows=2,587 width=8) (actual time=2.895..2.895 rows=2,587 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 123kB Buffers: shared hit=2 read=112
28.	1.932	1.932	↑ 1.0	2,587	1	→ Seq Scan on project (cost=0.00..139.87 rows=2,587 width=8) (actual time=0.004..1.932 rows=2,587 loops=1) Buffers: shared hit=2 read=112
29.	30.807	88.771	↑ 1.0	41,731	1	→ Hash (cost=1,520.31..1,520.31 rows=41,731 width=8) (actual time=88.771..88.771 rows=41,731 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 1980kB Buffers: shared hit=2 read=1101
30.	57.964	57.964	↑ 1.0	41,731	1	→ Seq Scan on task (cost=0.00..1,520.31 rows=41,731 width=8) (actual time=0.009..57.964 rows=41,731 loops=1) Buffers: shared hit=2 read=1101
31.	0.770	1.459	↑ 1.0	1,272	1	→ Hash (cost=23.72..23.72 rows=1,272 width=19) (actual time=1.459..1.459 rows=1,272 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 81kB Buffers: shared hit=2 read=9
32.	0.689	0.689	↑ 1.0	1,272	1	→ Seq Scan on userprojectroleratehistory (cost=0.00..23.72 rows=1,272 width=19) (actual time=0.004..0.689 rows=1,272 loops=1) Buffers: shared hit=2 read=9
33.	0.007	0.030	↑ 1.0	7	1	→ Hash (cost=1.61..1.61 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=2
34.	0.023	0.023	↑ 1.0	7	1	→ Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=7 width=19) (actual time=0.012..0.023 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.	4,397.297	65,554.383	↓ 2.0	3,446,124	1	→ Hash Full Join (cost=274,007.38..339,931.56 rows=1,725,468 width=188) (actual time=36,486.608..65,554.383 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, temp read=39451 written=58813
41.	3,452.332	61,157.080	↓ 2.0	3,446,124	1	→ Merge Full Join (cost=274,007.35..307,578.99 rows=1,725,468 width=136) (actual time=36,486.585..61,157.080 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=256129 read=116395, temp read=39451 written=58813
42.	4,582.693	37,501.892	↓ 8.9	1,720,840	1	→ Sort (cost=60,751.96..61,234.23 rows=192,911 width=92) (actual time=36,486.580..37,501.892 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, temp read=31439 written=43641
43.	2,771.183	32,919.199	↓ 8.9	1,720,840	1	→ Merge Full Join (cost=39,248.75..43,816.71 rows=192,911 width=92) (actual time=28,186.020..32,919.199 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=103446 read=80006, temp read=19349 written=31551
44.	4,849.515	11,686.971	↓ 9.3	1,600,460	1	→ Sort (cost=18,455.28..18,886.55 rows=172,509 width=44) (actual time=10,501.031..11,686.971 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.	6,837.456	6,837.456	↓ 9.3	1,600,460	1	→ CTE Scan on project_time (cost=0.00..3,450.18 rows=172,509 width=44) (actual time=3,292.587..6,837.456 rows=1,600,460 loops=1) Buffers: shared read=30461, temp written=6642
46.	5,475.216	18,461.045	↓ 8.3	1,600,258	1	→ Sort (cost=20,793.47..21,275.75 rows=192,911 width=48) (actual time=17,684.980..18,461.045 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.	12,985.829	12,985.829	↓ 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,081.333..12,985.829 rows=1,600,258 loops=1) Buffers: shared hit=103446 read=49545, temp written=5560
48.	5,127.519	20,202.856	↑ 1.0	1,725,284	1	→ Sort (cost=213,255.39..217,569.06 rows=1,725,468 width=44) (actual time=19,143.964..20,202.856 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.	15,075.337	15,075.337	↑ 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=93.344..15,075.337 rows=1,725,284 loops=1) Buffers: shared hit=152683 read=36389, temp written=7160
50.	0.002	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.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.	3,046.847	84,498.671	↓ 2.0	3,446,124	1	→ Hash Left Join (cost=103.29..525,939.66 rows=1,725,468 width=184) (actual time=36,487.841..84,498.671 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, temp read=39451 written=79366
54.	3,499.982	81,451.823	↓ 2.0	3,446,124	1	→ Hash Left Join (cost=67.87..131,634.80 rows=1,725,468 width=168) (actual time=36,487.832..81,451.823 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, temp read=39451 written=79366
55.	3,577.403	77,951.459	↓ 2.0	3,446,124	1	→ Hash Left Join (cost=45.92..99,260.33 rows=1,725,468 width=152) (actual time=36,487.442..77,951.459 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, temp read=39451 written=79366
56.	4,023.052	74,373.623	↓ 2.0	3,446,124	1	→ Hash Left Join (cost=30.94..66,892.83 rows=1,725,468 width=136) (actual time=36,487.000..74,373.623 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, temp read=39451 written=79366
57.	70,350.193	70,350.193	↓ 2.0	3,446,124	1	→ CTE Scan on pipeline1 (cost=0.00..34,509.36 rows=1,725,468 width=120) (actual time=36,486.610..70,350.193 rows=3,446,124 loops=1) Buffers: shared hit=256130 read=116395, temp read=39451 written=79366
58.	0.133	0.378	↑ 1.0	353	1	→ Hash (cost=26.53..26.53 rows=353 width=28) (actual time=0.378..0.378 rows=353 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 29kB Buffers: shared read=23
59.	0.245	0.245	↑ 1.0	353	1	→ Seq Scan on userlocation (cost=0.00..26.53 rows=353 width=28) (actual time=0.013..0.245 rows=353 loops=1) Buffers: shared read=23
60.	0.219	0.433	↑ 1.0	353	1	→ Hash (cost=10.57..10.57 rows=353 width=28) (actual time=0.433..0.433 rows=353 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 29kB Buffers: shared read=5
61.	0.214	0.214	↑ 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.214 rows=353 loops=1) Heap Fetches: 0 Buffers: shared read=5
62.	0.181	0.382	↑ 1.0	353	1	→ Hash (cost=17.53..17.53 rows=353 width=28) (actual time=0.382..0.382 rows=353 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 29kB Buffers: shared read=14
63.	0.201	0.201	↑ 1.0	353	1	→ Seq Scan on usercostcenter (cost=0.00..17.53 rows=353 width=28) (actual time=0.013..0.201 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.	94,656.296	94,656.296	↓ 2.0	3,446,124	1	→ CTE Scan on pipeline2 (cost=0.00..51,764.04 rows=1,725,468 width=184) (actual time=36,487.858..94,656.296 rows=3,446,124 loops=1) Buffers: shared hit=256130 read=116437, temp read=39451 written=107085

"Sort  (cost=1442734.39..1443165.76 rows=172547 width=208) (actual time=103670.463..103783.510 rows=294411 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=256130 read=116437, temp read=39451 written=107085"
"  ->  Subquery Scan on dmv_bi_projectcostbill0  (cost=1421686.56..1427725.71 rows=172547 width=208) (actual time=101170.265..102000.283 rows=294411 loops=1)"
"        Buffers: shared hit=256130 read=116437, temp read=39451 written=107085"
"        ->  HashAggregate  (cost=1421686.56..1426000.24 rows=172547 width=184) (actual time=101170.263..101676.481 rows=294411 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=256130 read=116437, temp read=39451 written=107085"
"              CTE project_time"
"                ->  HashAggregate  (cost=86526.39..88251.48 rows=172509 width=52) (actual time=3292.583..4800.329 rows=1600460 loops=1)"
"                      Group Key: allocation.entrydate, allocation.userid, allocation.projectid, allocation.taskid, allocation.timeoffcodeid, allocation.breaktypeid"
"                      Buffers: shared read=30461"
"                      ->  Seq Scan on dm_projecttimeallocation_facts allocation  (cost=0.00..47711.89 rows=1725089 width=52) (actual time=0.013..818.163 rows=1725282 loops=1)"
"                            Buffers: shared read=30461"
"              CTE project_billing"
"                ->  HashAggregate  (cost=119313.38..121724.77 rows=192911 width=26) (actual time=9081.329..10808.448 rows=1600258 loops=1)"
"                      Group Key: bi.entrydate, bidm.userid, bidm.projectid, bidm.taskid"
"                      Buffers: shared hit=103446 read=49545"
"                      InitPlan 2 (returns $1)"
"                        ->  Seq Scan on systeminformation  (cost=0.00..1.01 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)"
"                              Buffers: shared read=1"
"                      ->  Hash Join  (cost=58586.80..116418.70 rows=192911 width=26) (actual time=2937.580..6180.940 rows=1736318 loops=1)"
"                            Hash Cond: (bidm.billingitemid = bi.id)"
"                            Buffers: shared hit=103446 read=49545"
"                            ->  Seq Scan on billingitem2denormalizedmetadata bidm  (cost=0.00..49392.03 rows=1736203 width=28) (actual time=0.013..898.956 rows=1736318 loops=1)"
"                                  Buffers: shared hit=2 read=32028"
"                            ->  Hash  (cost=56175.25..56175.25 rows=192924 width=30) (actual time=2937.264..2937.264 rows=1736318 loops=1)"
"                                  Buckets: 2097152 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 118122kB"
"                                  Buffers: shared hit=103444 read=17517"
"                                  ->  Nested Loop  (cost=0.43..56175.25 rows=192924 width=30) (actual time=0.074..1957.874 rows=1736318 loops=1)"
"                                        Buffers: shared hit=103444 read=17517"
"                                        ->  Seq Scan on exchangerate  (cost=0.00..1.61 rows=7 width=19) (actual time=0.023..0.032 rows=7 loops=1)"
"                                              Filter: (variablecurrencyid = $1)"
"                                              Rows Removed by Filter: 42"
"                                              Buffers: shared read=2"
"                                        ->  Index Scan using dbatest1 on billingitem2 bi  (cost=0.43..6095.57 rows=192924 width=27) (actual time=0.009..130.679 rows=248045 loops=7)"
"                                              Index Cond: ((currencyid = exchangerate.fixedcurrencyid) AND (exchangerate.effectivedate <= entrydate) AND (exchangerate.enddate >= entrydate))"
"                                              Buffers: shared hit=103444 read=17515"
"              CTE project_cost"
"                ->  Hash Left Join  (cost=2257.06..233679.34 rows=1725468 width=130) (actual time=93.340..12703.399 rows=1725284 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"
"                      InitPlan 4 (returns $6)"
"                        ->  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"
"                      ->  Hash Left Join  (cost=2254.35..149560.07 rows=1725468 width=127) (actual time=93.270..8200.717 rows=1725284 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"
"                            ->  Hash Left Join  (cost=2214.73..117167.92 rows=1725468 width=120) (actual time=91.789..6265.137 rows=1725284 loops=1)"
"                                  Hash Cond: (dm_attendancetimeallocation_facts.taskid = task.id)"
"                                  Buffers: shared hit=152679 read=36380"
"                                  ->  Hash Left Join  (cost=172.78..92708.12 rows=1725468 width=116) (actual time=2.932..4576.306 rows=1725284 loops=1)"
"                                        Hash Cond: (dm_attendancetimeallocation_facts.projectid = project.id)"
"                                        Buffers: shared hit=152677 read=35279"
"                                        ->  Merge Left Join  (cost=0.58..69730.98 rows=1725468 width=112) (actual time=0.021..2955.193 rows=1725284 loops=1)"
"                                              Merge Cond: ((dm_attendancetimeallocation_facts.userid = usercostnormalizationfactor.userid) AND (dm_attendancetimeallocation_facts.entrydate = usercostnormalizationfactor.entrydate))"
"                                              Buffers: shared hit=152675 read=35167"
"                                              ->  Index Scan using ixata2userid on dm_attendancetimeallocation_facts  (cost=0.43..61078.45 rows=1725468 width=52) (actual time=0.013..1484.344 rows=1725284 loops=1)"
"                                                    Buffers: shared hit=152674 read=35167"
"                                              ->  Index Scan using uix2ucnf_useridentrydate on usercostnormalizationfactor  (cost=0.15..19.85 rows=580 width=68) (actual time=0.003..0.003 rows=0 loops=1)"
"                                                    Buffers: shared hit=1"
"                                        ->  Hash  (cost=139.87..139.87 rows=2587 width=8) (actual time=2.895..2.895 rows=2587 loops=1)"
"                                              Buckets: 4096  Batches: 1  Memory Usage: 123kB"
"                                              Buffers: shared hit=2 read=112"
"                                              ->  Seq Scan on project  (cost=0.00..139.87 rows=2587 width=8) (actual time=0.004..1.932 rows=2587 loops=1)"
"                                                    Buffers: shared hit=2 read=112"
"                                  ->  Hash  (cost=1520.31..1520.31 rows=41731 width=8) (actual time=88.771..88.771 rows=41731 loops=1)"
"                                        Buckets: 65536  Batches: 1  Memory Usage: 1980kB"
"                                        Buffers: shared hit=2 read=1101"
"                                        ->  Seq Scan on task  (cost=0.00..1520.31 rows=41731 width=8) (actual time=0.009..57.964 rows=41731 loops=1)"
"                                              Buffers: shared hit=2 read=1101"
"                            ->  Hash  (cost=23.72..23.72 rows=1272 width=19) (actual time=1.459..1.459 rows=1272 loops=1)"
"                                  Buckets: 2048  Batches: 1  Memory Usage: 81kB"
"                                  Buffers: shared hit=2 read=9"
"                                  ->  Seq Scan on userprojectroleratehistory  (cost=0.00..23.72 rows=1272 width=19) (actual time=0.004..0.689 rows=1272 loops=1)"
"                                        Buffers: shared hit=2 read=9"
"                      ->  Hash  (cost=1.61..1.61 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1)"
"                            Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                            Buffers: shared hit=2"
"                            ->  Seq Scan on exchangerate exchangerate_1  (cost=0.00..1.61 rows=7 width=19) (actual time=0.012..0.023 rows=7 loops=1)"
"                                  Filter: (variablecurrencyid = $6)"
"                                  Rows Removed by Filter: 42"
"                                  Buffers: shared hit=2"
"              CTE expense_cost"
"                ->  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"
"                      ->  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"
"                      ->  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))"
"              CTE pipeline1"
"                ->  Hash Full Join  (cost=274007.38..339931.56 rows=1725468 width=188) (actual time=36486.608..65554.383 rows=3446124 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, temp read=39451 written=58813"
"                      ->  Merge Full Join  (cost=274007.35..307578.99 rows=1725468 width=136) (actual time=36486.585..61157.080 rows=3446124 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=256129 read=116395, temp read=39451 written=58813"
"                            ->  Sort  (cost=60751.96..61234.23 rows=192911 width=92) (actual time=36486.580..37501.892 rows=1720840 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, temp read=31439 written=43641"
"                                  ->  Merge Full Join  (cost=39248.75..43816.71 rows=192911 width=92) (actual time=28186.020..32919.199 rows=1720840 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=103446 read=80006, temp read=19349 written=31551"
"                                        ->  Sort  (cost=18455.28..18886.55 rows=172509 width=44) (actual time=10501.031..11686.971 rows=1600460 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"
"                                              ->  CTE Scan on project_time  (cost=0.00..3450.18 rows=172509 width=44) (actual time=3292.587..6837.456 rows=1600460 loops=1)"
"                                                    Buffers: shared read=30461, temp written=6642"
"                                        ->  Sort  (cost=20793.47..21275.75 rows=192911 width=48) (actual time=17684.980..18461.045 rows=1600258 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"
"                                              ->  CTE Scan on project_billing  (cost=0.00..3858.22 rows=192911 width=48) (actual time=9081.333..12985.829 rows=1600258 loops=1)"
"                                                    Buffers: shared hit=103446 read=49545, temp written=5560"
"                            ->  Sort  (cost=213255.39..217569.06 rows=1725468 width=44) (actual time=19143.964..20202.856 rows=1725284 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"
"                                  ->  CTE Scan on project_cost  (cost=0.00..34509.36 rows=1725468 width=44) (actual time=93.344..15075.337 rows=1725284 loops=1)"
"                                        Buffers: shared hit=152683 read=36389, temp written=7160"
"                      ->  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"
"                            ->  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"
"              CTE pipeline2"
"                ->  Hash Left Join  (cost=103.29..525939.66 rows=1725468 width=184) (actual time=36487.841..84498.671 rows=3446124 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, temp read=39451 written=79366"
"                      ->  Hash Left Join  (cost=67.87..131634.80 rows=1725468 width=168) (actual time=36487.832..81451.823 rows=3446124 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, temp read=39451 written=79366"
"                            ->  Hash Left Join  (cost=45.92..99260.33 rows=1725468 width=152) (actual time=36487.442..77951.459 rows=3446124 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, temp read=39451 written=79366"
"                                  ->  Hash Left Join  (cost=30.94..66892.83 rows=1725468 width=136) (actual time=36487.000..74373.623 rows=3446124 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, temp read=39451 written=79366"
"                                        ->  CTE Scan on pipeline1  (cost=0.00..34509.36 rows=1725468 width=120) (actual time=36486.610..70350.193 rows=3446124 loops=1)"
"                                              Buffers: shared hit=256130 read=116395, temp read=39451 written=79366"
"                                        ->  Hash  (cost=26.53..26.53 rows=353 width=28) (actual time=0.378..0.378 rows=353 loops=1)"
"                                              Buckets: 1024  Batches: 1  Memory Usage: 29kB"
"                                              Buffers: shared read=23"
"                                              ->  Seq Scan on userlocation  (cost=0.00..26.53 rows=353 width=28) (actual time=0.013..0.245 rows=353 loops=1)"
"                                                    Buffers: shared read=23"
"                                  ->  Hash  (cost=10.57..10.57 rows=353 width=28) (actual time=0.433..0.433 rows=353 loops=1)"
"                                        Buckets: 1024  Batches: 1  Memory Usage: 29kB"
"                                        Buffers: shared read=5"
"                                        ->  Index Only Scan using ix4ud_userdivisionstartend on userdivision  (cost=0.27..10.57 rows=353 width=28) (actual time=0.034..0.214 rows=353 loops=1)"
"                                              Heap Fetches: 0"
"                                              Buffers: shared read=5"
"                            ->  Hash  (cost=17.53..17.53 rows=353 width=28) (actual time=0.382..0.382 rows=353 loops=1)"
"                                  Buckets: 1024  Batches: 1  Memory Usage: 29kB"
"                                  Buffers: shared read=14"
"                                  ->  Seq Scan on usercostcenter  (cost=0.00..17.53 rows=353 width=28) (actual time=0.013..0.201 rows=353 loops=1)"
"                                        Buffers: shared read=14"
"                      ->  Hash  (cost=21.30..21.30 rows=1130 width=28) (actual time=0.001..0.001 rows=0 loops=1)"
"                            Buckets: 2048  Batches: 1  Memory Usage: 16kB"
"                            ->  Seq Scan on userservicecenter  (cost=0.00..21.30 rows=1130 width=28) (actual time=0.001..0.001 rows=0 loops=1)"
"              ->  CTE Scan on pipeline2  (cost=0.00..51764.04 rows=1725468 width=184) (actual time=36487.858..94656.296 rows=3446124 loops=1)"
"                    Buffers: shared hit=256130 read=116437, temp read=39451 written=107085"
"Planning time: 19.232 ms"
"Execution time: 104056.416 ms"

Per node type stats

node type	count	sum of times	% of query
CTE Scan	6	199,905.115 ms	192.6 %
Hash	10	1,012.472 ms	1.0 %
Hash Full Join	1	4,397.297 ms	4.2 %
Hash Join	1	2,344.720 ms	2.3 %
Hash Left Join	8	23,802.332 ms	22.9 %
HashAggregate	3	15,629.850 ms	15.1 %
Index Only Scan	1	0.214 ms	0.0 %
Index Scan	5	2,399.102 ms	2.3 %
Merge Full Join	2	6,223.515 ms	6.0 %
Merge Left Join	1	1,470.846 ms	1.4 %
Nested Loop	2	1,043.091 ms	1.0 %
Seq Scan	12	1,778.218 ms	1.7 %
Sort	5	21,818.170 ms	21.0 %
Subquery Scan	1	323.802 ms	0.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
billingitem2	1	914.753 ms	0.9 %
Index Scan	1	914.753 ms	100.0 %
billingitem2denormalizedmetadata	1	898.956 ms	0.9 %
Seq Scan	1	898.956 ms	100.0 %
dm_attendancetimeallocation_facts	1	1,484.344 ms	1.4 %
Index Scan	1	1,484.344 ms	100.0 %
dm_projecttimeallocation_facts	1	818.163 ms	0.8 %
Seq Scan	1	818.163 ms	100.0 %
exchangerate	2	0.055 ms	0.0 %
Seq Scan	2	0.055 ms	100.0 %
expense	1	0.002 ms	0.0 %
Index Scan	1	0.002 ms	100.0 %
expenseentry	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
project	1	1.932 ms	0.0 %
Seq Scan	1	1.932 ms	100.0 %
systeminformation	2	0.012 ms	0.0 %
Seq Scan	2	0.012 ms	100.0 %
task	1	57.964 ms	0.1 %
Seq Scan	1	57.964 ms	100.0 %
usercostcenter	1	0.201 ms	0.0 %
Seq Scan	1	0.201 ms	100.0 %
usercostnormalizationfactor	1	0.003 ms	0.0 %
Index Scan	1	0.003 ms	100.0 %
userdivision	1	0.214 ms	0.0 %
Index Only Scan	1	0.214 ms	100.0 %
userlocation	1	0.245 ms	0.0 %
Seq Scan	1	0.245 ms	100.0 %
userprojectroleratehistory	1	0.689 ms	0.0 %
Seq Scan	1	0.689 ms	100.0 %
userservicecenter	1	0.001 ms	0.0 %
Seq Scan	1	0.001 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1lS : dashboard after index creation

Color mode:

Visible columns:

Per node type stats

Per table stats