explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HGmP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 14,176.653 ↑ 1,000.0 1 1

Limit (cost=517,025.74..538,090.74 rows=1,000 width=2,038) (actual time=14,176.650..14,176.653 rows=1 loops=1)

2.          

CTE taskowners

3. 27.081 1,130.778 ↑ 221.0 1 1

Gather (cost=1,000.84..52,892.68 rows=221 width=4) (actual time=0.905..1,130.778 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.002 1,103.697 ↓ 0.0 0 3 / 3

Nested Loop Semi Join (cost=0.84..51,870.58 rows=92 width=4) (actual time=727.486..1,103.697 rows=0 loops=3)

5. 0.002 1,103.690 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.43..50,941.13 rows=2,081 width=8) (actual time=727.480..1,103.690 rows=0 loops=3)

6. 1,103.685 1,103.685 ↓ 0.0 0 3 / 3

Parallel Seq Scan on taskkeyvalue taskkeyvalue4 (cost=0.00..37,766.56 rows=2,081 width=4) (actual time=727.475..1,103.685 rows=0 loops=3)

  • Filter: ((upper(keyuri) = 'URN:REPLICON:TASK-KEY-VALUE-KEY:ASSIGNED-USER'::text) AND (upper(uri) = 'URN:REPLICON-TENANT:B802550F8FA040E28174C57923F30EE9:USER:2'::text))
  • Rows Removed by Filter: 442,902
7. 0.003 0.003 ↑ 1.0 1 1 / 3

Index Scan using task_pkey on task task3 (cost=0.43..6.33 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (id = taskkeyvalue4.taskid)
8. 0.005 0.005 ↑ 1.0 1 1 / 3

Index Scan using uix4pkv_uniquekeyuri on projectkeyvalue projectkeyvalue5 (cost=0.41..0.45 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((projectid = task3.projectid) AND (upper(keyuri) = 'URN:REPLICON:PROJECT-KEY-VALUE-KEY:PROJECT-MANAGEMENT-TYPE'::text))
  • Filter: (upper(uri) = 'URN:REPLICON:PROJECT-MANAGEMENT-TYPE:MANAGED'::text)
9.          

Initplan (for Limit)

10. 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)

11. 0.012 14,176.650 ↑ 548,660.0 1 1

Result (cost=464,132.06..12,021,654.96 rows=548,660 width=2,038) (actual time=14,176.648..14,176.650 rows=1 loops=1)

12. 0.010 14,176.580 ↑ 548,660.0 1 1

Sort (cost=464,132.06..465,503.71 rows=548,660 width=1,005) (actual time=14,176.579..14,176.580 rows=1 loops=1)

  • Sort Key: parent.id, t.id
  • Sort Method: quicksort Memory: 25kB
13. 0.018 14,176.570 ↑ 548,660.0 1 1

Hash Left Join (cost=66,056.31..434,049.62 rows=548,660 width=1,005) (actual time=14,149.096..14,176.570 rows=1 loops=1)

  • Hash Cond: (currencyinfo.id = exchangerate.fixedcurrencyid)
  • Join Filter: ((exchangerate.enddate >= ((timezone(($9)::text, now()))::date)) AND (exchangerate.effectivedate <= ((timezone(($9)::text, now()))::date)))
14. 0.008 14,176.520 ↑ 548,660.0 1 1

Hash Join (cost=66,054.69..430,450.92 rows=548,660 width=948) (actual time=14,149.048..14,176.520 rows=1 loops=1)

  • Hash Cond: (sysinfo.basecurrencyid = currencyinfo2.id)
15. 0.025 14,176.495 ↑ 548,660.0 1 1

Hash Join (cost=66,053.53..428,137.55 rows=548,660 width=830) (actual time=14,149.023..14,176.495 rows=1 loops=1)

  • Hash Cond: (p.id = project6.id)
16. 483.263 14,164.315 ↑ 548,824.0 1 1

Hash Join (cost=65,346.37..425,989.11 rows=548,824 width=834) (actual time=14,136.844..14,164.315 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = taskowners.taskid)
17. 971.876 12,550.326 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,336.90..416,992.65 rows=1,097,648 width=838) (actual time=1,184.659..12,550.326 rows=1,097,641 loops=1)

18. 0.010 0.010 ↑ 1.0 1 1

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

19. 965.601 11,578.440 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,336.90..406,015.16 rows=1,097,648 width=716) (actual time=1,184.648..11,578.440 rows=1,097,641 loops=1)

20. 0.011 0.114 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.112..0.114 rows=1 loops=1)

21.          

Initplan (for Result)

22. 0.054 0.103 ↑ 1.0 1 1

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

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

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

24. 0.002 0.006 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 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.003..0.004 rows=1 loops=1)

26. 1,118.325 10,612.725 ↑ 1.0 1,097,641 1

Hash Left Join (cost=65,332.61..395,034.37 rows=1,097,648 width=712) (actual time=1,184.531..10,612.725 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
27. 1,042.199 9,494.385 ↑ 1.0 1,097,641 1

Hash Left Join (cost=65,331.46..390,407.53 rows=1,097,648 width=472) (actual time=1,184.502..9,494.385 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
28. 1,131.478 7,277.051 ↑ 1.0 1,097,641 1

Hash Join (cost=400.33..190,747.08 rows=1,097,648 width=432) (actual time=9.282..7,277.051 rows=1,097,641 loops=1)

  • Hash Cond: (t.projectid = p.id)
29. 1,622.032 6,136.361 ↑ 1.0 1,097,641 1

Merge Join (cost=4.83..187,469.02 rows=1,097,648 width=428) (actual time=0.045..6,136.361 rows=1,097,641 loops=1)

  • Merge Cond: (t.id = tdh.taskid)
30. 1,597.294 3,304.866 ↑ 1.0 1,097,647 1

Merge Left Join (cost=2.29..114,495.91 rows=1,097,650 width=318) (actual time=0.030..3,304.866 rows=1,097,647 loops=1)

  • Merge Cond: (t.id = tf.taskid)
31. 1,113.465 1,113.465 ↑ 1.0 1,097,647 1

Index Scan using task_pkey on task t (cost=0.43..59,240.09 rows=1,097,650 width=282) (actual time=0.015..1,113.465 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
32. 594.107 594.107 ↑ 1.0 1,097,646 1

Index Scan using dm_tasklist_facts_pkey on dm_tasklist_facts tf (cost=0.43..38,791.13 rows=1,097,646 width=40) (actual time=0.011..594.107 rows=1,097,646 loops=1)

33. 1,209.463 1,209.463 ↑ 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=110) (actual time=0.011..1,209.463 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
34. 4.658 9.212 ↑ 1.0 10,008 1

Hash (cost=270.41..270.41 rows=10,008 width=4) (actual time=9.211..9.212 rows=10,008 loops=1)

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

Index Only Scan using project_pkey on project p (cost=0.29..270.41 rows=10,008 width=4) (actual time=0.017..4.554 rows=10,008 loops=1)

  • Heap Fetches: 0
36. 607.900 1,175.135 ↑ 1.0 1,097,648 1

Hash (cost=41,562.50..41,562.50 rows=1,097,650 width=44) (actual time=1,175.135..1,175.135 rows=1,097,648 loops=1)

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

Seq Scan on task parent (cost=0.00..41,562.50 rows=1,097,650 width=44) (actual time=0.005..567.235 rows=1,097,648 loops=1)

38. 0.006 0.015 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=240) (actual time=0.015..0.015 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.009 0.009 ↑ 1.0 7 1

Seq Scan on currencyinfo (cost=0.00..1.07 rows=7 width=240) (actual time=0.006..0.009 rows=7 loops=1)

40. 0.005 1,130.726 ↑ 200.0 1 1

Hash (cost=6.97..6.97 rows=200 width=4) (actual time=1,130.726..1,130.726 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.009 1,130.721 ↑ 200.0 1 1

HashAggregate (cost=4.97..6.97 rows=200 width=4) (actual time=1,130.720..1,130.721 rows=1 loops=1)

  • Group Key: taskowners.taskid
42. 1,130.712 1,130.712 ↑ 221.0 1 1

CTE Scan on taskowners (cost=0.00..4.42 rows=221 width=4) (actual time=0.908..1,130.712 rows=1 loops=1)

43. 4.836 12.155 ↑ 1.0 10,005 1

Hash (cost=582.10..582.10 rows=10,005 width=4) (actual time=12.154..12.155 rows=10,005 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 480kB
44. 7.319 7.319 ↑ 1.0 10,005 1

Seq Scan on project project6 (cost=0.00..582.10 rows=10,005 width=4) (actual time=0.011..7.319 rows=10,005 loops=1)

  • Filter: (projectstatuslabelid = 'c721a296-1f22-426d-84dd-bc1f0467162a'::uuid)
  • Rows Removed by Filter: 3
45. 0.007 0.017 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=122) (actual time=0.017..0.017 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
46. 0.010 0.010 ↑ 1.0 7 1

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

47. 0.007 0.032 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=32) (actual time=0.031..0.032 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.025 0.025 ↓ 7.0 7 1

Seq Scan on exchangerate (cost=0.00..1.61 rows=1 width=32) (actual time=0.018..0.025 rows=7 loops=1)

  • Filter: (variablecurrencyid = $8)
  • Rows Removed by Filter: 42
49.          

SubPlan (for Result)

50. 0.033 0.033 ↓ 0.0 0 1

Index Only Scan using ixtkparentid on task i (cost=0.43..4.45 rows=1 width=0) (actual time=0.032..0.033 rows=0 loops=1)

  • Index Cond: (parentid = t.id)
  • Heap Fetches: 0
51. 0.021 0.021 ↑ 1.0 1 1

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

  • Index Cond: (id = t.projectid)
52. 0.004 0.004 ↑ 1.0 1 1

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

  • Index Cond: (id = t.projectid)
Planning time : 6.820 ms
Execution time : 14,177.067 ms