explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ekbc : dbdtrial01 - Master

Settings
# exclusive inclusive rows x rows loops node
1. 0.346 23,281,299.077 ↓ 40.0 40 1

Unique (cost=18,211.68..18,211.69 rows=1 width=118) (actual time=23,281,298.489..23,281,299.077 rows=40 loops=1)

  • Buffers: shared hit=309926514 read=44850 dirtied=14 written=6
2. 1.851 23,281,298.731 ↓ 1,090.0 1,090 1

Sort (cost=18,211.68..18,211.69 rows=1 width=118) (actual time=23,281,298.478..23,281,298.731 rows=1,090 loops=1)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 98kB
  • Buffers: shared hit=309926514 read=44850 dirtied=14 written=6
3. 1.250 23,281,296.880 ↓ 1,090.0 1,090 1

Nested Loop Semi Join (cost=5,266.71..18,211.67 rows=1 width=118) (actual time=2,633,775.902..23,281,296.880 rows=1,090 loops=1)

  • Buffers: shared hit=309926507 read=44850 dirtied=14 written=6
4. 2.576 23,281,292.306 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=5,266.43..18,211.36 rows=1 width=130) (actual time=2,633,775.884..23,281,292.306 rows=1,108 loops=1)

  • Buffers: shared hit=309923185 read=44848 dirtied=14 written=6
5. 2.445 23,281,285.298 ↓ 1,108.0 1,108 1

Nested Loop (cost=5,266.16..18,211.04 rows=1 width=16) (actual time=2,633,775.850..23,281,285.298 rows=1,108 loops=1)

  • Buffers: shared hit=309920304 read=44801 dirtied=14 written=6
6. 24,735.716 23,281,217.481 ↓ 1,108.0 1,108 1

Nested Loop (cost=5,265.87..18,210.71 rows=1 width=64) (actual time=2,633,775.333..23,281,217.481 rows=1,108 loops=1)

  • Join Filter: (ts.id = at.timesheetid)
  • Rows Removed by Join Filter: 55261476
  • Buffers: shared hit=309917141 read=44633 dirtied=14 written=6
7. 184.141 2,684.575 ↓ 49,166.0 49,166 1

Nested Loop (cost=1.26..35.90 rows=1 width=44) (actual time=4.219..2,684.575 rows=49,166 loops=1)

  • Buffers: shared hit=199688 read=37094 dirtied=1 written=6
8. 67.999 435.462 ↓ 4,097.2 49,166 1

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

  • Buffers: shared hit=32190 read=7544 written=1
9. 1.465 13.879 ↓ 539.0 539 1

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

  • Buffers: shared hit=1588 read=38
10. 1.753 6.485 ↓ 539.0 539 1

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

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

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

  • Filter: (departmentid = ANY ('{1,3}'::integer[]))
  • Buffers: shared read=6
12. 0.570 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.508 0.508 ↑ 1.0 2 1

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

  • Buffers: shared read=1
14. 5.929 5.929 ↑ 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.010..0.011 rows=1 loops=539)

  • Index Cond: (id = du.userid)
  • Heap Fetches: 539
  • Buffers: shared hit=1588 read=31
15. 353.584 353.584 ↑ 2.7 91 539

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

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=30602 read=7506 written=1
16. 2,064.972 2,064.972 ↑ 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.040..0.042 rows=1 loops=49,166)

  • Index Cond: (timesheetid = ts.id)
  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
  • Buffers: shared hit=167498 read=29550 dirtied=1 written=5
17. 287,914.045 23,253,797.190 ↑ 1.3 1,124 49,166

Hash Left Join (cost=5,264.61..18,141.08 rows=1,499 width=89) (actual time=401.653..472.965 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=309717453 read=7539 dirtied=13
18. 33,726.162 22,965,881.094 ↑ 1.3 1,124 49,166

Hash Left Join (cost=5,228.25..17,327.05 rows=1,499 width=61) (actual time=401.620..467.109 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=309715499 read=7287 dirtied=12
19. 4,259,245.520 22,932,153.218 ↑ 1.3 1,124 49,166

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

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=309715487 read=7263 dirtied=12
20. 18,672,804.306 18,672,804.306 ↓ 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.007..379.791 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=309715261 read=6299 dirtied=3
21. 0.908 103.392 ↑ 1.3 1,124 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 117kB
  • Buffers: shared hit=223 read=964 dirtied=9
22. 102.484 102.484 ↑ 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=9.157..102.484 rows=1,124 loops=1)

  • Index Cond: ((entrydate >= '2019-05-01'::date) AND (entrydate <= '2019-05-31'::date))
  • Buffers: shared hit=223 read=964 dirtied=9
23. 0.006 1.714 ↓ 2.0 4 1

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

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

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

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

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

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 565
  • Buffers: shared read=21
26. 0.056 0.056 ↑ 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.013..0.014 rows=1 loops=4)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=1603 read=4
28. 0.515 1.857 ↓ 226.5 453 1

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

  • Buffers: shared hit=1603 read=4
29. 0.212 0.212 ↓ 188.3 565 1

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

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=21
30. 1.130 1.130 ↑ 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.002 rows=1 loops=565)

  • Index Cond: (projectid = pj_1.id)
  • Buffers: shared hit=1582 read=4
31. 65.372 65.372 ↑ 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.059..0.059 rows=1 loops=1,108)

  • Index Cond: (id = ts.id)
  • Heap Fetches: 1108
  • Buffers: shared hit=3163 read=168
32. 4.432 4.432 ↑ 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.004..0.004 rows=1 loops=1,108)

  • Index Cond: (at.projectid = id)
  • Buffers: shared hit=2881 read=47
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=3322 read=2
Planning time : 45.494 ms
Execution time : 23,281,299.594 ms