explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AFts

Settings
# exclusive inclusive rows x rows loops node
1. 139.958 33,706.128 ↓ 179.9 21,414 1

Sort (cost=1,946,363.23..1,946,363.53 rows=119 width=321) (actual time=33,698.046..33,706.128 rows=21,414 loops=1)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename3, ((login.loginname)::character varying(255)) COLLATE "en_US", ((userinfo7.displayname)::text) COLLATE "en_US", dmv_timesheetday_facts5.timesheetstartdate4, dmv_timesheetday_facts5.timesheetenddate5, dmv_timesheetday_facts5.entrydate2, ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ui.id, ts.id
  • Sort Method: quicksort Memory: 6153kB
  • Buffers: shared hit=1342696 read=267149 dirtied=551
2.          

CTE dmv_timesheetday_facts0cte

3. 53.530 32,997.815 ↑ 1.1 21,431 1

HashAggregate (cost=1,945,068.86..1,945,308.80 rows=23,994 width=61) (actual time=32,980.594..32,997.815 rows=21,431 loops=1)

  • Group Key: ts_1.userid, at.entrydate, ui_1.duplicatename, ts_2.startdate, ts_2.enddate, tslist_1.timesheetstatus, at.projectid, at.taskid, ts_1.id
  • Buffers: shared hit=934077 read=267041 dirtied=551
4. 30.527 32,944.285 ↑ 1.1 21,696 1

Nested Loop (cost=85,717.90..1,944,469.01 rows=23,994 width=61) (actual time=13,265.008..32,944.285 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = ts_2.id)
  • Buffers: shared hit=934077 read=267041 dirtied=551
5. 36.613 31,850.654 ↑ 1.1 21,696 1

Nested Loop (cost=85,717.47..1,933,365.94 rows=23,994 width=85) (actual time=13,262.864..31,850.654 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = tslist_1.timesheetid)
  • Buffers: shared hit=848711 read=265456 dirtied=551
6. 659.188 30,772.633 ↑ 1.1 21,696 1

Hash Join (cost=85,717.05..1,921,533.25 rows=24,111 width=65) (actual time=13,261.443..30,772.633 rows=21,696 loops=1)

  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=765284 read=261937 dirtied=551
7. 8,131.758 27,699.376 ↑ 1.0 1,581,081 1

Hash Left Join (cost=77,107.28..1,890,917.82 rows=1,582,877 width=97) (actual time=9,891.366..27,699.376 rows=1,581,081 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=752620 read=255864 dirtied=551
8. 897.743 19,567.589 ↑ 1.0 1,581,081 1

Hash Left Join (cost=77,090.92..945,436.50 rows=1,582,877 width=65) (actual time=9,890.515..19,567.589 rows=1,581,081 loops=1)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=752591 read=255864 dirtied=551
9. 4,674.348 18,669.831 ↑ 1.0 1,581,081 1

Hash Right Join (cost=77,084.41..933,558.39 rows=1,582,877 width=69) (actual time=9,890.485..18,669.831 rows=1,581,081 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=752590 read=255864 dirtied=551
10. 4,119.642 4,119.642 ↑ 1.0 7,214,206 1

Index Scan using dbatest on dm_attendancetimeallocation_metadata isbillable (cost=0.56..805,838.85 rows=7,341,643 width=17) (actual time=1.992..4,119.642 rows=7,214,206 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Buffers: shared read=217478
11. 1,005.657 9,875.841 ↑ 1.0 1,581,081 1

Hash (cost=57,297.88..57,297.88 rows=1,582,877 width=84) (actual time=9,875.841..9,875.841 rows=1,581,081 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 175283kB
  • Buffers: shared hit=752590 read=38386 dirtied=551
12. 8,870.184 8,870.184 ↑ 1.0 1,581,081 1

Index Scan using dbatest1 on dm_attendancetimeallocation_facts at (cost=0.43..57,297.88 rows=1,582,877 width=84) (actual time=1.994..8,870.184 rows=1,581,081 loops=1)

  • Index Cond: ((entrydate >= '2018-01-20'::date) AND (entrydate <= '2018-08-08'::date))
  • Buffers: shared hit=752590 read=38386 dirtied=551
13. 0.000 0.015 ↓ 0.0 0 1

Hash (cost=6.44..6.44 rows=5 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
14. 0.002 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.15..6.44 rows=5 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Buffers: shared hit=1
15. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..1.15 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=1
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.15..5.24 rows=5 width=16) (never executed)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
17. 0.001 0.029 ↓ 0.0 0 1

Hash (cost=15.60..15.60 rows=61 width=44) (actual time=0.029..0.029 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=13
18. 0.006 0.028 ↓ 0.0 0 1

Nested Loop (cost=0.15..15.60 rows=61 width=44) (actual time=0.028..0.028 rows=0 loops=1)

  • Buffers: shared hit=13
19. 0.010 0.010 ↑ 1.0 12 1

Seq Scan on project pj_1 (cost=0.00..1.15 rows=12 width=4) (actual time=0.004..0.010 rows=12 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=1
20. 0.012 0.012 ↓ 0.0 0 12

Index Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.15..1.15 rows=5 width=44) (actual time=0.001..0.001 rows=0 loops=12)

  • Index Cond: (projectid = pj_1.id)
  • Buffers: shared hit=12
21. 8.796 2,414.069 ↑ 1.1 11,738 1

Hash (cost=8,446.82..8,446.82 rows=13,036 width=21) (actual time=2,414.069..2,414.069 rows=11,738 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 736kB
  • Buffers: shared hit=12664 read=6073
22. 13.279 2,405.273 ↑ 1.1 11,738 1

Merge Join (cost=0.84..8,446.82 rows=13,036 width=21) (actual time=2.288..2,405.273 rows=11,738 loops=1)

  • Merge Cond: (ui_1.id = ts_1.userid)
  • Buffers: shared hit=12664 read=6073
23. 7.657 7.657 ↑ 1.0 7,089 1

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..294.95 rows=7,178 width=5) (actual time=0.006..7.657 rows=7,089 loops=1)

  • Buffers: shared hit=6580 read=181
24. 2,384.337 2,384.337 ↑ 1.1 11,738 1

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.42..7,971.58 rows=13,036 width=20) (actual time=1.373..2,384.337 rows=11,738 loops=1)

  • Index Cond: (userid = ANY ('{3087,3094,3104,7061,6789,948,1303,804,815,7086,2738,341,3279,3308,3684,861,873,6747,886,3334,898,3362,7116,373,916,155,929,2771,939,953,966,981,775,5451,997,1007,7197,3417,1023,2366,1034,1045,1060,1071,1082,3078,1110,1124,787,1137,1149,1160,1173,1185,1194,414,1210,1223,1237,1251,1265,1277,1290,1729,746,1315,1328,1350,447,6866,1365,1384,1397,1409,807,1423,2281,1452,1463,482,820,1473,1486,1498,1511,1522,1533,835,1549,1564,1575,1589,1602,5383,1629,1642,3444,662,509,1655}'::integer[]))
  • Buffers: shared hit=6084 read=5892
25. 1,041.408 1,041.408 ↑ 1.0 1 21,696

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist_1 (cost=0.42..0.48 rows=1 width=20) (actual time=0.047..0.048 rows=1 loops=21,696)

  • Index Cond: (timesheetid = at.timesheetid)
  • Buffers: shared hit=83427 read=3519
26. 1,063.104 1,063.104 ↑ 1.0 1 21,696

Index Scan using timesheet_pkey on timesheet ts_2 (cost=0.42..0.45 rows=1 width=24) (actual time=0.048..0.049 rows=1 loops=21,696)

  • Index Cond: (id = at.timesheetid)
  • Buffers: shared hit=85366 read=1585
27.          

Initplan (forSort)

28. 0.636 0.636 ↑ 1.0 1 1

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

  • Buffers: shared read=1
29. 19.649 33,565.534 ↓ 179.9 21,414 1

Hash Left Join (cost=19.99..1,049.32 rows=119 width=321) (actual time=32,986.008..33,565.534 rows=21,414 loops=1)

  • Hash Cond: (tk.id = tdh.taskid)
  • Buffers: shared hit=1342684 read=267149 dirtied=551
30. 15.098 33,545.364 ↓ 179.9 21,414 1

Hash Left Join (cost=11.57..1,039.27 rows=119 width=259) (actual time=32,985.460..33,545.364 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.taskid8 = tk.id)
  • Buffers: shared hit=1342683 read=267145 dirtied=551
31. 14.621 33,528.479 ↓ 179.9 21,414 1

Nested Loop (cost=3.25..1,029.60 rows=119 width=233) (actual time=32,983.647..33,528.479 rows=21,414 loops=1)

  • Buffers: shared hit=1342681 read=267142 dirtied=551
32. 26.127 33,428.202 ↓ 178.4 21,414 1

Nested Loop (cost=2.82..967.32 rows=120 width=249) (actual time=32,983.067..33,428.202 rows=21,414 loops=1)

  • Buffers: shared hit=1256865 read=267141 dirtied=551
33. 15.559 33,337.833 ↓ 178.4 21,414 1

Hash Left Join (cost=2.40..783.02 rows=120 width=233) (actual time=32,982.532..33,337.833 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.projectid7 = pj.id)
  • Buffers: shared hit=1192456 read=267140 dirtied=551
34. 29.531 33,322.253 ↓ 178.4 21,414 1

Nested Loop Left Join (cost=1.13..781.23 rows=120 width=99) (actual time=32,982.488..33,322.253 rows=21,414 loops=1)

  • Buffers: shared hit=1192455 read=267140 dirtied=551
35. 18.747 33,249.894 ↓ 178.4 21,414 1

Nested Loop Left Join (cost=0.85..740.88 rows=120 width=88) (actual time=32,982.476..33,249.894 rows=21,414 loops=1)

  • Buffers: shared hit=1127947 read=267140 dirtied=551
36. 24.080 33,166.905 ↓ 178.4 21,414 1

Nested Loop (cost=0.56..701.69 rows=120 width=84) (actual time=32,982.033..33,166.905 rows=21,414 loops=1)

  • Join Filter: (dmv_timesheetday_facts5.userid1 = ui.id)
  • Buffers: shared hit=1063025 read=267081 dirtied=551
37. 17.043 33,099.997 ↓ 178.4 21,414 1

Nested Loop (cost=0.28..661.07 rows=120 width=71) (actual time=32,982.016..33,099.997 rows=21,414 loops=1)

  • Buffers: shared hit=998531 read=267081 dirtied=551
38. 33,018.712 33,018.712 ↓ 178.4 21,414 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..539.87 rows=120 width=61) (actual time=32,980.604..33,018.712 rows=21,414 loops=1)

  • Filter: (timesheetstatus6 = 2)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=934077 read=267041 dirtied=551
39. 64.242 64.242 ↑ 1.0 1 21,414

Index Scan using login_pkey on login (cost=0.28..1.00 rows=1 width=10) (actual time=0.002..0.003 rows=1 loops=21,414)

  • Index Cond: (userid = dmv_timesheetday_facts5.userid1)
  • Buffers: shared hit=64454 read=40
40. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.33 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: (id = login.userid)
  • Buffers: shared hit=64494
41. 64.242 64.242 ↑ 1.0 1 21,414

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy12 (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=21,414)

  • Index Cond: ((ui.id = userid) AND ('2019-01-20'::date >= startdate))
  • Filter: ('2019-01-20'::date <= enddate)
  • Buffers: shared hit=64922 read=59
42. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using userinfo_pkey on userinfo userinfo7 (cost=0.28..0.33 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: (userhierarchy12.supervisorid = id)
  • Buffers: shared hit=64508
43. 0.010 0.021 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=138) (actual time=0.021..0.021 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
44. 0.011 0.011 ↑ 1.0 12 1

Seq Scan on project pj (cost=0.00..1.12 rows=12 width=138) (actual time=0.003..0.011 rows=12 loops=1)

  • Buffers: shared hit=1
45. 64.242 64.242 ↑ 1.0 1 21,414

Index Only Scan using timesheet_pkey on timesheet ts (cost=0.42..1.53 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=21,414)

  • Index Cond: (id = dmv_timesheetday_facts5.timesheetid9)
  • Heap Fetches: 0
  • Buffers: shared hit=64409 read=1
46. 85.656 85.656 ↑ 1.0 1 21,414

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.51 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=21,414)

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 21414
  • Buffers: shared hit=85816 read=1
47. 0.058 1.787 ↑ 1.0 152 1

Hash (cost=6.42..6.42 rows=152 width=30) (actual time=1.787..1.787 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=2 read=3
48. 0.097 1.729 ↑ 1.0 152 1

Hash Left Join (cost=1.13..6.42 rows=152 width=30) (actual time=1.608..1.729 rows=152 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=2 read=3
49. 0.537 0.537 ↑ 1.0 152 1

Seq Scan on task tk (cost=0.00..4.52 rows=152 width=34) (actual time=0.499..0.537 rows=152 loops=1)

  • Buffers: shared hit=2 read=1
50. 0.007 1.095 ↑ 1.0 2 1

Hash (cost=1.11..1.11 rows=2 width=4) (actual time=1.095..1.095 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
51. 1.088 1.088 ↑ 1.0 2 1

Seq Scan on exchangerate (cost=0.00..1.11 rows=2 width=4) (actual time=1.087..1.088 rows=2 loops=1)

  • Filter: ((variablecurrencyid = $4) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 2
  • Buffers: shared read=2
52. 0.065 0.521 ↑ 1.0 152 1

Hash (cost=6.52..6.52 rows=152 width=66) (actual time=0.521..0.521 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=1 read=4
53. 0.456 0.456 ↑ 1.0 152 1

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..6.52 rows=152 width=66) (actual time=0.002..0.456 rows=152 loops=1)

  • Buffers: shared hit=1 read=4