explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YquC : FUJIFILMDimatixG3 - RR

Settings
# exclusive inclusive rows x rows loops node
1. 1.742 1,228,280.368 ↓ 231.0 231 1

Sort (cost=356,051.23..356,051.24 rows=1 width=152) (actual time=1,228,280.284..1,228,280.368 rows=231 loops=1)

  • Sort Key: ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename4, ((login.loginname)::characte (...)
  • Sort Method: quicksort Memory: 86kB
  • Buffers: shared hit=361441531 read=56682 written=4673
2.          

CTE dmv_timesheetday_facts0cte

3. 0.714 1,228,258.975 ↓ 231.0 231 1

HashAggregate (cost=356,044.37..356,044.38 rows=1 width=31) (actual time=1,228,258.815..1,228,258.975 rows=231 loops=1)

  • Group Key: ts.userid, at.entrydate, upper(at.comments), ui_1.duplicatename, at.timeoffcodeid
  • Buffers: shared hit=361438059 read=56672 written=4669
4. 0.599 1,228,258.261 ↓ 231.0 231 1

Nested Loop (cost=197.08..356,044.35 rows=1 width=31) (actual time=1,149,018.114..1,228,258.261 rows=231 loops=1)

  • Buffers: shared hit=361438059 read=56672 written=4669
5. 35,320.926 1,228,223.012 ↓ 1.0 231 1

Nested Loop (cost=196.65..355,928.25 rows=222 width=63) (actual time=1,149,016.550..1,228,223.012 rows=231 loops=1)

  • Join Filter: (timesheet3.id = at.timesheetid)
  • Rows Removed by Join Filter: 101964709
  • Buffers: shared hit=361437191 read=56616 written=4669
6. 1.355 14,645.732 ↓ 803.0 803 1

Nested Loop (cost=0.70..590.09 rows=1 width=21) (actual time=9.214..14,645.732 rows=803 loops=1)

  • Buffers: shared hit=9 read=781 written=42
7. 0.025 0.025 ↑ 1.0 1 1

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..2.29 rows=1 width=5) (actual time=0.023..0.025 rows=1 loops=1)

  • Index Cond: (id = 166)
  • Buffers: shared hit=2 read=1
8. 14,644.352 14,644.352 ↓ 1.0 803 1

Index Scan using uix2tsuseridstartdate on timesheet timesheet3 (cost=0.42..579.91 rows=788 width=16) (actual time=9.188..14,644.352 rows=803 loops=1)

  • Index Cond: (userid = 166)
  • Buffers: shared hit=7 read=780 written=42
9. 213,460.687 1,178,256.354 ↓ 1.6 126,980 803

Nested Loop Left Join (cost=195.95..353,560.33 rows=79,015 width=95) (actual time=0.664..1,467.318 rows=126,980 loops=803)

  • Buffers: shared hit=361437182 read=55835 written=4627
10. 62,079.927 556,935.907 ↓ 1.6 126,980 803

Hash Left Join (cost=195.52..195,011.63 rows=79,015 width=110) (actual time=0.655..693.569 rows=126,980 loops=803)

  • Hash Cond: (at.projectid = pj.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=55016502 read=31640 written=2311
11. 119,281.635 494,855.977 ↓ 1.6 126,980 803

Nested Loop Left Join (cost=0.58..194,146.40 rows=79,015 width=78) (actual time=0.654..616.259 rows=126,980 loops=803)

  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate) AND (pj_1.id = at.projectid) AND (pc_1.clientid = at.userspecifiedclientid))
  • Buffers: shared hit=55016502 read=31640 written=2311
12. 375,574.342 375,574.342 ↓ 1.6 126,980 803

Index Scan using ixata2userid on dm_attendancetimeallocation_facts at (cost=0.43..192,248.29 rows=79,015 width=82) (actual time=0.644..467.714 rows=126,980 loops=803)

  • Index Cond: ((entrydate >= '2018-06-03'::date) AND (entrydate <= '2019-05-11'::date))
  • Filter: (timeoffcodeid = ANY ('{1,24}'::integer[]))
  • Rows Removed by Filter: 584856
  • Buffers: shared hit=55016499 read=31560 written=2311
13. 0.000 0.000 ↓ 0.0 0 101,964,940

Materialize (cost=0.15..120.28 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=101,964,940)

  • Buffers: shared hit=3 read=80
14. 0.009 5.970 ↓ 0.0 0 1

Nested Loop (cost=0.15..120.28 rows=1 width=16) (actual time=5.970..5.970 rows=0 loops=1)

  • Buffers: shared hit=3 read=80
15. 5.957 5.957 ↑ 1.0 1 1

Seq Scan on project pj_1 (cost=0.00..114.99 rows=1 width=4) (actual time=5.956..5.957 rows=1 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 2638
  • Buffers: shared hit=2 read=80
16. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.15..5.24 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (projectid = pj_1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=1
17. 0.002 0.003 ↓ 0.0 0 1

Hash (cost=182.19..182.19 rows=1,020 width=44) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 0.000 0.001 ↓ 0.0 0 1

Hash Join (cost=147.96..182.19 rows=1,020 width=44) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (pc.projectid = pj.id)
19. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on projectclient pc (cost=0.00..20.20 rows=1,020 width=44) (actual time=0.001..0.001 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Hash (cost=114.99..114.99 rows=2,638 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on project pj (cost=0.00..114.99 rows=2,638 width=4) (never executed)

  • Filter: (clientbillingallocationmethod = 0)
22. 407,859.760 407,859.760 ↓ 0.0 0 101,964,940

Index Scan using ixatamfactid on dm_attendancetimeallocation_metadata isbillable (cost=0.43..1.48 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=101,964,940)

  • Index Cond: (factid = at.id)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=306420680 read=24195 written=2316
23. 34.650 34.650 ↑ 1.0 1 231

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.51 rows=1 width=20) (actual time=0.149..0.150 rows=1 loops=231)

  • Index Cond: (id = at.timesheetid)
  • Filter: (userid = 166)
  • Buffers: shared hit=868 read=56
24. 0.619 1,228,278.626 ↓ 231.0 231 1

Nested Loop Left Join (cost=1.42..6.84 rows=1 width=152) (actual time=1,228,270.818..1,228,278.626 rows=231 loops=1)

  • Buffers: shared hit=361441516 read=56682 written=4673
25. 0.384 1,228,277.083 ↓ 231.0 231 1

Nested Loop Left Join (cost=1.15..6.51 rows=1 width=140) (actual time=1,228,270.346..1,228,277.083 rows=231 loops=1)

  • Buffers: shared hit=361440824 read=56681 written=4672
26. 0.397 1,228,274.389 ↓ 231.0 231 1

Nested Loop (cost=0.87..6.16 rows=1 width=136) (actual time=1,228,268.636..1,228,274.389 rows=231 loops=1)

  • Buffers: shared hit=361440134 read=56678 written=4670
27. 0.482 1,228,270.989 ↓ 231.0 231 1

Nested Loop (cost=0.87..4.27 rows=1 width=116) (actual time=1,228,268.026..1,228,270.989 rows=231 loops=1)

  • Join Filter: (dmv_timesheetday_facts5.userid1 = du.userid)
  • Buffers: shared hit=361439904 read=56677 written=4670
28. 0.429 1,228,268.428 ↓ 231.0 231 1

Nested Loop (cost=0.59..3.96 rows=1 width=116) (actual time=1,228,266.388..1,228,268.428 rows=231 loops=1)

  • Join Filter: (dmv_timesheetday_facts5.userid1 = login.userid)
  • Buffers: shared hit=361439444 read=56674 written=4669
29. 0.259 1,228,261.069 ↓ 231.0 231 1

Nested Loop (cost=0.31..3.64 rows=1 width=103) (actual time=1,228,259.971..1,228,261.069 rows=231 loops=1)

  • Buffers: shared hit=361438752 read=56673 written=4669
30. 0.198 1,228,260.117 ↓ 231.0 231 1

Hash Right Join (cost=0.03..1.33 rows=1 width=76) (actual time=1,228,259.953..1,228,260.117 rows=231 loops=1)

  • Hash Cond: (timeoffcode7.id = dmv_timesheetday_facts5.timeoffcodeid5)
  • Buffers: shared hit=361438059 read=56673 written=4669
31. 0.541 0.541 ↑ 1.0 21 1

Seq Scan on timeoffcode timeoffcode7 (cost=0.00..1.21 rows=21 width=23) (actual time=0.535..0.541 rows=21 loops=1)

  • Buffers: shared read=1
32. 0.136 1,228,259.378 ↓ 231.0 231 1

Hash (cost=0.02..0.02 rows=1 width=61) (actual time=1,228,259.378..1,228,259.378 rows=231 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=361438059 read=56672 written=4669
33. 1,228,259.242 1,228,259.242 ↓ 231.0 231 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..0.02 rows=1 width=61) (actual time=1,228,258.822..1,228,259.242 rows=231 loops=1)

  • Buffers: shared hit=361438059 read=56672 written=4669
34. 0.693 0.693 ↑ 1.0 1 231

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.29 rows=1 width=27) (actual time=0.002..0.003 rows=1 loops=231)

  • Index Cond: (id = dmv_timesheetday_facts5.userid1)
  • Buffers: shared hit=693
35. 6.930 6.930 ↑ 1.0 1 231

Index Scan using login_pkey on login (cost=0.28..0.31 rows=1 width=13) (actual time=0.030..0.030 rows=1 loops=231)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=692 read=1
36. 2.079 2.079 ↑ 1.0 1 231

Index Only Scan using departmentusers_pkey on departmentusers du (cost=0.28..0.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=231)

  • Index Cond: ((departmentid = 43) AND (userid = ui.id))
  • Heap Fetches: 0
  • Buffers: shared hit=460 read=3 written=1
37. 3.003 3.003 ↑ 1.0 1 231

Seq Scan on departments dep (cost=0.00..1.88 rows=1 width=28) (actual time=0.011..0.013 rows=1 loops=231)

  • Filter: (id = 43)
  • Rows Removed by Filter: 69
  • Buffers: shared hit=230 read=1
38. 2.310 2.310 ↑ 1.0 1 231

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy10 (cost=0.28..0.35 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=231)

  • Index Cond: ((ui.id = userid) AND ('2019-06-04'::date >= startdate))
  • Filter: ('2019-06-04'::date <= enddate)
  • Buffers: shared hit=690 read=3 written=2
39. 0.924 0.924 ↑ 1.0 1 231

Index Scan using userinfo_pkey on userinfo userinfo9 (cost=0.28..0.32 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=231)

  • Index Cond: (userhierarchy10.supervisorid = id)
  • Buffers: shared hit=692 read=1 written=1
Planning time : 51.324 ms
Execution time : 1,228,284.660 ms