explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0nM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1.049 ↓ 0.0 0 1

Sort (cost=4,164,692.34..4,164,692.34 rows=1 width=2,325) (actual time=1.049..1.049 rows=0 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype, cl.id, cl.name, cl.code, cl.slug, ((cl.name)::character varying(255))
  • Sort Key: cl.name COLLATE "en_US
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=28
2.          

Initplan (forSort)

3. 0.017 0.017 ↑ 1.0 1 1

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters (cost=0.42..2.44 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: parameters.projectleaderid
  • Index Cond: (parameters.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
4. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_1 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_1.projectleaderid
  • Index Cond: (parameters_1.backend_pid = pg_backend_pid())
5. 0.004 0.004 ↑ 1.0 1 1

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_2 (cost=0.42..2.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: parameters_2.enddate
  • Index Cond: (parameters_2.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
6. 0.005 0.005 ↑ 1.0 1 1

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_3 (cost=0.42..2.44 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: parameters_3.startdate
  • Index Cond: (parameters_3.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
7. 0.010 1.040 ↓ 0.0 0 1

Hash Join (cost=4,158,986.91..4,164,682.58 rows=1 width=2,325) (actual time=1.040..1.040 rows=0 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype, cl.id, cl.name, cl.code, cl.slug, cl.name
  • Hash Cond: (ta.projectid = pj.id)
  • Buffers: shared hit=28
8. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=4,158,961.40..4,161,357.88 rows=239,648 width=4) (never executed)

  • Output: ta.projectid
  • Group Key: ta.projectid
9. 0.000 0.000 ↓ 0.0 0

Append (cost=7,953.31..4,158,362.28 rows=239,648 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7,953.31..2,077,982.14 rows=122,558 width=4) (never executed)

  • Output: ta.projectid
  • Hash Cond: (ttma.taskid = ta.id)
11.          

Initplan (forHash Join)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_7 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_7.userid
  • Index Cond: (parameters_7.backend_pid = pg_backend_pid())
13. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=311.43..2,067,276.32 rows=490,229 width=4) (never executed)

  • Output: ttma.taskid
  • Hash Cond: (ttma.projectteammemberid = ptm.id)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".taskteammemberassignment ttma (cost=0.00..1,711,910.12 rows=93,376,912 width=20) (never executed)

  • Output: ttma.id, ttma.taskid, ttma.projectteammemberid, ttma.taskallocationstartdate, ttma.taskallocationenddate, ttma.taskallocationduration
15. 0.000 0.000 ↓ 0.0 0

Hash (cost=308.88..308.88 rows=204 width=16) (never executed)

  • Output: ptm.id
16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.66..308.88 rows=204 width=16) (never executed)

  • Output: ptm.id
17. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.37..6.34 rows=1 width=4) (never executed)

  • Output: dfh.parentid
  • Hash Cond: (dfh.childid = departmentusers.departmentid)
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".departmentflathierarchy dfh (cost=0.00..1.70 rows=70 width=8) (never executed)

  • Output: dfh.parentid, dfh.childid, dfh.depth
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.33..4.33 rows=3 width=4) (never executed)

  • Output: departmentusers.departmentid
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on "635227c70a4e4433a8667687a66f96eb".departmentusers (cost=0.28..4.33 rows=3 width=4) (never executed)

  • Output: departmentusers.departmentid
  • Index Cond: (departmentusers.userid = $8)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2ptm_projectdept on "635227c70a4e4433a8667687a66f96eb".projectteammember ptm (cost=0.29..300.59 rows=195 width=20) (never executed)

  • Output: ptm.id, ptm.projectid, ptm.userid, ptm.departmentid, ptm.placeholderprojectroleid, ptm.index, ptm.placeholderdisplayname, ptm.locationid, ptm.divisionid, ptm.costcenterid, ptm.servicecenterid
  • Index Cond: (ptm.departmentid = dfh.parentid)
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=6,683.62..6,683.62 rows=76,466 width=8) (never executed)

  • Output: ta.projectid, ta.id
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".task ta (cost=0.00..6,683.62 rows=76,466 width=8) (never executed)

  • Output: ta.projectid, ta.id
  • Filter: ((NOT ta.isclosed) AND ta.istimeentryallowed)
24. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7,795.02..2,077,466.36 rows=116,890 width=4) (never executed)

  • Output: ta_1.projectid
  • Hash Cond: (ttma_1.taskid = ta_1.id)
25.          

Initplan (forHash Join)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_6 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_6.userid
  • Index Cond: (parameters_6.backend_pid = pg_backend_pid())
27. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=153.14..2,066,902.24 rows=467,556 width=4) (never executed)

  • Output: ttma_1.taskid
  • Hash Cond: (ttma_1.projectteammemberid = ptm_1.id)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".taskteammemberassignment ttma_1 (cost=0.00..1,711,910.12 rows=93,376,912 width=20) (never executed)

  • Output: ttma_1.id, ttma_1.taskid, ttma_1.projectteammemberid, ttma_1.taskallocationstartdate, ttma_1.taskallocationenddate, ttma_1.taskallocationduration
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=150.70..150.70 rows=195 width=16) (never executed)

  • Output: ptm_1.id
30. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2ptm_projectuser on "635227c70a4e4433a8667687a66f96eb".projectteammember ptm_1 (cost=0.29..150.70 rows=195 width=16) (never executed)

  • Output: ptm_1.id
  • Index Cond: (ptm_1.userid = $7)
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=6,683.62..6,683.62 rows=76,466 width=8) (never executed)

  • Output: ta_1.projectid, ta_1.id
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".task ta_1 (cost=0.00..6,683.62 rows=76,466 width=8) (never executed)

  • Output: ta_1.projectid, ta_1.id
  • Filter: ((NOT ta_1.isclosed) AND ta_1.istimeentryallowed)
33. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=31.77..337.78 rows=102 width=4) (never executed)

  • Output: ptm_2.projectid
  • Hash Cond: (ptm_2.projectid = pr.id)
34.          

Initplan (forHash Join)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_5 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_5.userid
  • Index Cond: (parameters_5.backend_pid = pg_backend_pid())
36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.66..308.88 rows=204 width=4) (never executed)

  • Output: ptm_2.projectid
37. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.37..6.34 rows=1 width=4) (never executed)

  • Output: dfh_1.parentid
  • Hash Cond: (dfh_1.childid = departmentusers_1.departmentid)
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".departmentflathierarchy dfh_1 (cost=0.00..1.70 rows=70 width=8) (never executed)

  • Output: dfh_1.parentid, dfh_1.childid, dfh_1.depth
39. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.33..4.33 rows=3 width=4) (never executed)

  • Output: departmentusers_1.departmentid
40. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on "635227c70a4e4433a8667687a66f96eb".departmentusers departmentusers_1 (cost=0.28..4.33 rows=3 width=4) (never executed)

  • Output: departmentusers_1.departmentid
  • Index Cond: (departmentusers_1.userid = $5)
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2ptm_projectdept on "635227c70a4e4433a8667687a66f96eb".projectteammember ptm_2 (cost=0.29..300.59 rows=195 width=8) (never executed)

  • Output: ptm_2.projectid, ptm_2.departmentid
  • Index Cond: (ptm_2.departmentid = dfh_1.parentid)
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.95..20.95 rows=298 width=4) (never executed)

  • Output: pr.id
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".project pr (cost=0.00..20.95 rows=298 width=4) (never executed)

  • Output: pr.id
  • Filter: pr.istimeentryallowed
44. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=27.40..179.53 rows=98 width=4) (never executed)

  • Output: ptm_3.projectid
  • Hash Cond: (ptm_3.projectid = pr_1.id)
45.          

Initplan (forHash Join)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "635227c70a4e4433a8667687a66f96eb".parameters parameters_4 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_4.userid
  • Index Cond: (parameters_4.backend_pid = pg_backend_pid())
47. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2ptm_projectuser on "635227c70a4e4433a8667687a66f96eb".projectteammember ptm_3 (cost=0.29..150.70 rows=195 width=4) (never executed)

  • Output: ptm_3.userid, ptm_3.projectid
  • Index Cond: (ptm_3.userid = $4)
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.95..20.95 rows=298 width=4) (never executed)

  • Output: pr_1.id
49. 0.000 0.000 ↓ 0.0 0

Seq Scan on "635227c70a4e4433a8667687a66f96eb".project pr_1 (cost=0.00..20.95 rows=298 width=4) (never executed)

  • Output: pr_1.id
  • Filter: pr_1.istimeentryallowed
50. 0.000 1.030 ↓ 0.0 0 1

Hash (cost=25.50..25.50 rows=1 width=2,329) (actual time=1.030..1.030 rows=0 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype, pc.projectid, cl.id, cl.name, cl.code, cl.slug
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=28
51. 0.001 1.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..25.50 rows=1 width=2,329) (actual time=1.030..1.030 rows=0 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype, pc.projectid, cl.id, cl.name, cl.code, cl.slug
  • Buffers: shared hit=28
52. 0.439 1.029 ↓ 0.0 0 1

Nested Loop (cost=0.28..25.27 rows=1 width=1,179) (actual time=1.029..1.029 rows=0 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype, pc.projectid, pc.clientid
  • Buffers: shared hit=28
53. 0.379 0.590 ↓ 503.0 503 1

Nested Loop (cost=0.00..23.60 rows=1 width=1,171) (actual time=0.037..0.590 rows=503 loops=1)

  • Output: pj.id, pj.name, pj.code, pj.slug, pj.startdate, pj.enddate, pj.istimeentryallowed, pj.billingtype, pj.timeandexpenseentrytype
  • Join Filter: (pj.projectstatuslabelid = psl.id)
  • Rows Removed by Join Filter: 92
  • Buffers: shared hit=20
54. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on "635227c70a4e4433a8667687a66f96eb".projectstatuslabel psl (cost=0.00..1.09 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=1)

  • Output: psl.id, psl.name, psl.description, psl.projectstatustype, psl.enabled
  • Filter: (psl.projectstatustype = 1)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
55. 0.200 0.200 ↓ 99.2 595 1

Seq Scan on "635227c70a4e4433a8667687a66f96eb".project pj (cost=0.00..22.44 rows=6 width=1,187) (actual time=0.022..0.200 rows=595 loops=1)

  • Output: pj.id, pj.guidid, pj.name, pj.slug, pj.code, pj.clientid, pj.projectleaderapproverid, pj.projectstatuslabelid, pj.isprojectleaderapprovalrequired, pj.startdate, pj.enddate, pj.programid, pj.billingtype, pj.fixe (...)
  • Filter: (($0 IS NULL) OR ($1 = pj.projectleaderapproverid))
  • Buffers: shared hit=19
56. 0.000 0.000 ↓ 0.0 0 503

Index Only Scan using uix4pc_projectclienteffectiveend on "635227c70a4e4433a8667687a66f96eb".projectclient pc (cost=0.28..1.66 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=503)

  • Output: pc.projectid, pc.clientid, pc.effectivedate, pc.enddate
  • Index Cond: ((pc.projectid = pj.id) AND (pc.effectivedate <= $2) AND (pc.enddate >= $3))
  • Heap Fetches: 0
57. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_pkey on "635227c70a4e4433a8667687a66f96eb".clients cl (cost=0.14..0.22 rows=1 width=1,154) (never executed)

  • Output: cl.id, cl.name, cl.slug, cl.code, cl.comments, cl.clientmanageruserid, cl.disabled, cl.address, cl.city, cl.stateprovince, cl.zippostalcode, cl.country, cl.email, cl.telephone, cl.fax, cl.website, cl.billingcontact, cl.bil (...)
  • Index Cond: (pc.clientid = cl.id)