explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GToK

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 29,912.166 ↑ 1.0 1 1

Limit (cost=362,452.75..362,469.37 rows=1 width=3,351) (actual time=29,912.163..29,912.166 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.005 0.005 ↑ 1.0 1 1

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

4. 0.010 29,912.163 ↑ 1.0 1 1

Result (cost=362,451.74..362,468.36 rows=1 width=3,351) (actual time=29,912.162..29,912.163 rows=1 loops=1)

5. 0.020 29,912.114 ↑ 1.0 1 1

Sort (cost=362,451.74..362,451.75 rows=1 width=2,319) (actual time=29,912.113..29,912.114 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
6. 0.925 29,912.094 ↑ 1.0 1 1

Nested Loop (cost=77,496.96..362,451.73 rows=1 width=2,319) (actual time=29,886.091..29,912.094 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
  • Rows Removed by Join Filter: 2,424
7. 0.007 29,909.584 ↑ 1.0 1 1

Nested Loop Left Join (cost=77,496.96..361,744.57 rows=1 width=408) (actual time=29,883.581..29,909.584 rows=1 loops=1)

  • Join Filter: ((exchangerate.enddate >= ((timezone(($3)::text, now()))::date)) AND (exchangerate.effectivedate <= ((timezone(($3)::text, now()))::date)) AND (exchangerate.fixedcurrencyid = currencyinfo.id))
  • Rows Removed by Join Filter: 7
8. 0.006 29,909.549 ↑ 1.0 1 1

Nested Loop (cost=77,496.96..361,742.94 rows=1 width=416) (actual time=29,883.547..29,909.549 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
9. 476.218 29,909.533 ↑ 1.0 1 1

Hash Semi Join (cost=77,496.96..361,741.78 rows=1 width=420) (actual time=29,883.533..29,909.533 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = task3.id)
10. 916.965 10,621.988 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,620.85..346,984.34 rows=1,097,648 width=424) (actual time=1,202.030..10,621.988 rows=1,097,641 loops=1)

11. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on systeminformation sysinfo (cost=0.00..1.01 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=1)

12. 906.374 9,705.010 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,620.85..336,006.85 rows=1,097,648 width=420) (actual time=1,202.016..9,705.010 rows=1,097,641 loops=1)

13. 0.016 0.135 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.134..0.135 rows=1 loops=1)

14.          

Initplan (for Result)

15. 0.056 0.119 ↑ 1.0 1 1

Hash Join (cost=1.02..4.28 rows=1 width=15) (actual time=0.113..0.119 rows=1 loops=1)

  • Hash Cond: (timezone.id = systeminformation_1.systemtimezoneid)
16. 0.055 0.055 ↑ 1.0 91 1

Seq Scan on timezone (cost=0.00..2.91 rows=91 width=31) (actual time=0.014..0.055 rows=91 loops=1)

17. 0.004 0.008 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.002..0.004 rows=1 loops=1)

19. 1,024.645 8,798.501 ↑ 1.0 1,097,641 1

Hash Left Join (cost=65,616.57..325,026.06 rows=1,097,648 width=416) (actual time=1,201.879..8,798.501 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
20. 961.891 7,773.774 ↑ 1.0 1,097,641 1

Hash Left Join (cost=65,615.41..320,399.09 rows=1,097,648 width=416) (actual time=1,201.767..7,773.774 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
21. 1,057.655 5,640.815 ↑ 1.0 1,097,641 1

Hash Join (cost=684.33..135,744.69 rows=1,097,648 width=376) (actual time=30.388..5,640.815 rows=1,097,641 loops=1)

  • Hash Cond: (t.projectid = p.id)
22. 1,616.857 4,555.559 ↑ 1.0 1,097,641 1

Merge Join (cost=2.15..132,179.96 rows=1,097,648 width=372) (actual time=2.759..4,555.559 rows=1,097,641 loops=1)

  • Merge Cond: (t.id = tdh.taskid)
23. 1,808.302 1,808.302 ↑ 1.0 1,097,647 1

Index Scan using task_pkey on task t (cost=0.43..59,204.34 rows=1,097,648 width=274) (actual time=0.756..1,808.302 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
24. 1,130.400 1,130.400 ↑ 1.0 1,097,642 1

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.43..56,515.75 rows=1,097,648 width=98) (actual time=1.997..1,130.400 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
25. 5.016 27.601 ↑ 1.0 10,008 1

Hash (cost=557.08..557.08 rows=10,008 width=4) (actual time=27.600..27.601 rows=10,008 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 480kB
26. 22.585 22.585 ↑ 1.0 10,008 1

Seq Scan on project p (cost=0.00..557.08 rows=10,008 width=4) (actual time=0.588..22.585 rows=10,008 loops=1)

27. 604.434 1,171.068 ↑ 1.0 1,097,648 1

Hash (cost=41,562.48..41,562.48 rows=1,097,648 width=44) (actual time=1,171.067..1,171.068 rows=1,097,648 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,092kB
28. 566.634 566.634 ↑ 1.0 1,097,648 1

Seq Scan on task parent (cost=0.00..41,562.48 rows=1,097,648 width=44) (actual time=0.007..566.634 rows=1,097,648 loops=1)

29. 0.010 0.082 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.081..0.082 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.072 0.072 ↑ 1.0 7 1

Seq Scan on currencyinfo (cost=0.00..1.07 rows=7 width=4) (actual time=0.068..0.072 rows=7 loops=1)

31. 0.006 18,811.327 ↑ 1.0 1 1

Hash (cost=11,876.10..11,876.10 rows=1 width=8) (actual time=18,811.327..18,811.327 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 849.532 18,811.321 ↑ 1.0 1 1

Nested Loop (cost=1,999.50..11,876.10 rows=1 width=8) (actual time=870.833..18,811.321 rows=1 loops=1)

33. 924.634 6,012.775 ↓ 87.8 1,086,274 1

Nested Loop (cost=1,998.95..2,688.23 rows=12,367 width=4) (actual time=90.322..6,012.775 rows=1,086,274 loops=1)

34. 15.030 96.678 ↓ 9,963.0 9,963 1

HashAggregate (cost=1,998.52..1,998.53 rows=1 width=4) (actual time=88.786..96.678 rows=9,963 loops=1)

  • Group Key: projectkeyvalue5.projectid
35. 81.648 81.648 ↓ 9,963.0 9,963 1

Seq Scan on projectkeyvalue projectkeyvalue5 (cost=0.00..1,998.52 rows=1 width=4) (actual time=0.612..81.648 rows=9,963 loops=1)

  • Filter: ((upper(keyuri) = 'URN:REPLICON:PROJECT-KEY-VALUE-KEY:PROJECT-MANAGEMENT-TYPE'::text) AND (upper(uri) = 'URN:REPLICON:PROJECT-MANAGEMENT-TYPE:MANAGED'::text))
  • Rows Removed by Filter: 39,913
36. 4,991.463 4,991.463 ↑ 2.8 109 9,963

Index Scan using ixtkprojectid on task task3 (cost=0.43..686.62 rows=308 width=8) (actual time=0.051..0.501 rows=109 loops=9,963)

  • Index Cond: (projectid = projectkeyvalue5.projectid)
37. 11,949.014 11,949.014 ↓ 0.0 0 1,086,274

Index Scan using uix4tkv_uniquekeyuri on taskkeyvalue taskkeyvalue4 (cost=0.55..0.73 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1,086,274)

  • Index Cond: ((taskid = task3.id) AND (upper(keyuri) = 'URN:REPLICON:TASK-KEY-VALUE-KEY:ASSIGNED-USER'::text))
  • Filter: (upper(uri) = 'URN:REPLICON-TENANT:B802550F8FA040E28174C57923F30EE9:USER:2'::text)
  • Rows Removed by Filter: 1
38. 0.010 0.010 ↑ 7.0 1 1

Seq Scan on currencyinfo currencyinfo2 (cost=0.00..1.07 rows=7 width=4) (actual time=0.010..0.010 rows=1 loops=1)

39. 0.028 0.028 ↓ 7.0 7 1

Seq Scan on exchangerate (cost=0.00..1.61 rows=1 width=12) (actual time=0.022..0.028 rows=7 loops=1)

  • Filter: (variablecurrencyid = $2)
  • Rows Removed by Filter: 42
40. 1.585 1.585 ↑ 4.1 2,425 1

Seq Scan on project project6 (cost=0.00..582.10 rows=10,005 width=4) (actual time=0.009..1.585 rows=2,425 loops=1)

  • Filter: (projectstatuslabelid = 'c721a296-1f22-426d-84dd-bc1f0467162a'::uuid)
41.          

SubPlan (for Result)

42. 0.029 0.029 ↑ 1.0 1 1

Index Scan using project_pkey on project project1 (cost=0.29..8.30 rows=1 width=516) (actual time=0.027..0.029 rows=1 loops=1)

  • Index Cond: (id = t.projectid)
43. 0.010 0.010 ↑ 1.0 1 1

Index Scan using project_pkey on project project2 (cost=0.29..8.30 rows=1 width=516) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = t.projectid)
Planning time : 19.901 ms
Execution time : 29,912.552 ms