explain.depesz.com

PostgreSQL's explain analyze made readable

Result: um4b : dbdtrial01 - RR

Settings
# exclusive inclusive rows x rows loops node
1. 0.247 22,784,306.729 ↓ 40.0 40 1

Unique (cost=18,211.68..18,211.69 rows=1 width=118) (actual time=22,784,306.277..22,784,306.729 rows=40 loops=1)

  • Buffers: shared hit=309937876 read=70324 written=179
2. 1.970 22,784,306.482 ↓ 1,090.0 1,090 1

Sort (cost=18,211.68..18,211.69 rows=1 width=118) (actual time=22,784,306.275..22,784,306.482 rows=1,090 loops=1)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 98kB
  • Buffers: shared hit=309937876 read=70324 written=179
3. 1.677 22,784,304.512 ↓ 1,090.0 1,090 1

Nested Loop Semi Join (cost=5,266.71..18,211.67 rows=1 width=118) (actual time=2,666,507.806..22,784,304.512 rows=1,090 loops=1)

  • Buffers: shared hit=309937872 read=70321 written=179
4. 1.915 22,784,299.511 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=5,266.43..18,211.36 rows=1 width=130) (actual time=2,666,507.783..22,784,299.511 rows=1,108 loops=1)

  • Buffers: shared hit=309934549 read=70320 written=179
5. 2.042 22,784,156.880 ↓ 1,108.0 1,108 1

Nested Loop (cost=5,266.16..18,211.04 rows=1 width=16) (actual time=2,666,501.867..22,784,156.880 rows=1,108 loops=1)

  • Buffers: shared hit=309931527 read=70262 written=179
6. 19,575.947 22,784,041.822 ↓ 1,108.0 1,108 1

Nested Loop (cost=5,265.87..18,210.71 rows=1 width=64) (actual time=2,666,499.139..22,784,041.822 rows=1,108 loops=1)

  • Join Filter: (ts.id = at.timesheetid)
  • Rows Removed by Join Filter: 55261476
  • Buffers: shared hit=309927809 read=70068 written=179
7. 260.029 9,064.427 ↓ 49,166.0 49,166 1

Nested Loop (cost=1.26..35.90 rows=1 width=44) (actual time=6.634..9,064.427 rows=49,166 loops=1)

  • Buffers: shared hit=210241 read=62271 written=179
8. 73.917 593.676 ↓ 4,097.2 49,166 1

Nested Loop (cost=0.84..27.90 rows=12 width=28) (actual time=4.222..593.676 rows=49,166 loops=1)

  • Buffers: shared hit=31742 read=8278 written=42
9. 1.406 31.425 ↓ 539.0 539 1

Nested Loop (cost=0.55..19.73 rows=1 width=8) (actual time=2.420..31.425 rows=539 loops=1)

  • Buffers: shared hit=1615 read=41
10. 1.668 11.154 ↓ 539.0 539 1

Nested Loop (cost=0.28..17.83 rows=1 width=4) (actual time=1.460..11.154 rows=539 loops=1)

  • Join Filter: (du.departmentid = dep.id)
  • Rows Removed by Join Filter: 539
  • Buffers: shared read=7
11. 8.408 8.408 ↓ 107.8 539 1

Index Scan using ixduuserid on departmentusers du (cost=0.28..16.65 rows=5 width=8) (actual time=1.003..8.408 rows=539 loops=1)

  • Filter: (departmentid = ANY ('{1,3}'::integer[]))
  • Buffers: shared read=6
12. 0.638 1.078 ↑ 1.0 2 539

Materialize (cost=0.00..1.03 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=539)

  • Buffers: shared read=1
13. 0.440 0.440 ↑ 1.0 2 1

Seq Scan on departments dep (cost=0.00..1.02 rows=2 width=4) (actual time=0.439..0.440 rows=2 loops=1)

  • Buffers: shared read=1
14. 18.865 18.865 ↑ 1.0 1 539

Index Only Scan using userinfo_pkey on userinfo ui (cost=0.28..1.89 rows=1 width=4) (actual time=0.022..0.035 rows=1 loops=539)

  • Index Cond: (id = du.userid)
  • Heap Fetches: 569
  • Buffers: shared hit=1615 read=34
15. 488.334 488.334 ↑ 2.7 91 539

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.29..5.70 rows=247 width=20) (actual time=0.012..0.906 rows=91 loops=539)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=30127 read=8237 written=42
16. 8,210.722 8,210.722 ↑ 1.0 1 49,166

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.66 rows=1 width=16) (actual time=0.096..0.167 rows=1 loops=49,166)

  • Index Cond: (timesheetid = ts.id)
  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
  • Buffers: shared hit=178499 read=53993 written=137
17. 239,976.353 22,755,401.448 ↑ 1.3 1,124 49,166

Hash Left Join (cost=5,264.61..18,141.08 rows=1,499 width=89) (actual time=390.799..462.828 rows=1,124 loops=49,166)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=309717568 read=7797
18. 29,201.742 22,515,422.202 ↑ 1.3 1,124 49,166

Hash Left Join (cost=5,228.25..17,327.05 rows=1,499 width=61) (actual time=390.758..457.947 rows=1,124 loops=49,166)

  • 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=309715829 read=7322
19. 3,633,659.657 22,486,217.598 ↑ 1.3 1,124 49,166

Hash Right Join (cost=5,191.89..17,279.43 rows=1,499 width=65) (actual time=390.755..457.353 rows=1,124 loops=49,166)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=309715817 read=7298
20. 18,852,407.704 18,852,407.704 ↓ 140.5 270,724 49,166

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..12,080.21 rows=1,927 width=17) (actual time=0.008..383.444 rows=270,724 loops=49,166)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 59848
  • Buffers: shared hit=309714774 read=6299
21. 0.781 150.237 ↑ 1.3 1,124 1

Hash (cost=5,173.15..5,173.15 rows=1,499 width=80) (actual time=150.237..150.237 rows=1,124 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
  • Buffers: shared hit=1040 read=999
22. 149.456 149.456 ↑ 1.3 1,124 1

Index Scan using ixata2userid on dm_attendancetimeallocation_facts at (cost=0.42..5,173.15 rows=1,499 width=80) (actual time=8.549..149.456 rows=1,124 loops=1)

  • Index Cond: ((entrydate >= '2019-05-01'::date) AND (entrydate <= '2019-05-31'::date))
  • Buffers: shared hit=1040 read=999
23. 0.008 2.862 ↓ 2.0 4 1

Hash (cost=36.33..36.33 rows=2 width=16) (actual time=2.862..2.862 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=9 read=24
24. 0.016 2.854 ↓ 2.0 4 1

Nested Loop (cost=0.27..36.33 rows=2 width=16) (actual time=2.546..2.854 rows=4 loops=1)

  • Buffers: shared hit=9 read=24
25. 1.434 1.434 ↓ 1.3 4 1

Seq Scan on project pj_2 (cost=0.00..28.35 rows=3 width=4) (actual time=1.140..1.434 rows=4 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 565
  • Buffers: shared read=21
26. 1.404 1.404 ↑ 2.0 1 4

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.27..2.64 rows=2 width=16) (actual time=0.351..0.351 rows=1 loops=4)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 4
  • Buffers: shared hit=9 read=3
27. 0.184 2.893 ↓ 226.5 453 1

Hash (cost=36.33..36.33 rows=2 width=44) (actual time=2.893..2.893 rows=453 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=1606 read=4
28. 0.261 2.709 ↓ 226.5 453 1

Nested Loop (cost=0.27..36.33 rows=2 width=44) (actual time=0.021..2.709 rows=453 loops=1)

  • Buffers: shared hit=1606 read=4
29. 0.188 0.188 ↓ 188.3 565 1

Seq Scan on project pj_1 (cost=0.00..28.35 rows=3 width=4) (actual time=0.007..0.188 rows=565 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=21
30. 2.260 2.260 ↑ 2.0 1 565

Index Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.27..2.64 rows=2 width=44) (actual time=0.002..0.004 rows=1 loops=565)

  • Index Cond: (projectid = pj_1.id)
  • Buffers: shared hit=1585 read=4
31. 113.016 113.016 ↑ 1.0 1 1,108

Index Only Scan using timesheet_pkey on timesheet ts_1 (cost=0.29..0.32 rows=1 width=16) (actual time=0.099..0.102 rows=1 loops=1,108)

  • Index Cond: (id = ts.id)
  • Heap Fetches: 1689
  • Buffers: shared hit=3718 read=194
32. 140.716 140.716 ↑ 1.0 1 1,108

Index Scan using project_pkey on project pj (cost=0.28..0.31 rows=1 width=122) (actual time=0.125..0.127 rows=1 loops=1,108)

  • Index Cond: (at.projectid = id)
  • Buffers: shared hit=3022 read=58
33. 3.324 3.324 ↑ 1.0 1 1,108

Index Scan using userinfo_pkey on userinfo userinfo6 (cost=0.28..0.29 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,108)

  • Index Cond: (id = ts.userid)
  • Filter: (NOT disabled)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3323 read=1
Planning time : 81.153 ms
Execution time : 22,784,308.674 ms