explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AVA6

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,709.026 ↑ 18.0 1 1

Limit (cost=420,385.29..420,684.40 rows=18 width=3,351) (actual time=10,709.023..10,709.026 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.011 10,709.023 ↑ 18.0 1 1

Result (cost=420,384.28..420,683.39 rows=18 width=3,351) (actual time=10,709.021..10,709.023 rows=1 loops=1)

5. 0.009 10,708.986 ↑ 18.0 1 1

Sort (cost=420,384.28..420,384.32 rows=18 width=2,319) (actual time=10,708.985..10,708.986 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
6. 0.006 10,708.977 ↑ 18.0 1 1

Nested Loop (cost=88,524.43..420,383.90 rows=18 width=2,319) (actual time=10,682.220..10,708.977 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
7. 0.007 10,708.949 ↑ 18.0 1 1

Nested Loop Left Join (cost=88,524.14..420,378.15 rows=18 width=409) (actual time=10,682.194..10,708.949 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.005 10,708.912 ↑ 18.0 1 1

Nested Loop (cost=88,524.14..420,376.18 rows=18 width=417) (actual time=10,682.157..10,708.912 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
9. 483.798 10,708.890 ↑ 18.0 1 1

Hash Semi Join (cost=88,524.14..420,373.44 rows=18 width=421) (actual time=10,682.137..10,708.890 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = tdh_1.taskid)
10. 938.431 10,169.380 ↓ 1.0 1,097,641 1

Nested Loop (cost=65,336.73..394,307.35 rows=1,096,562 width=425) (actual time=1,210.576..10,169.380 rows=1,097,641 loops=1)

11. 0.010 0.010 ↑ 1.0 1 1

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

12. 921.317 9,230.939 ↓ 1.0 1,097,641 1

Nested Loop (cost=65,336.73..383,340.72 rows=1,096,562 width=421) (actual time=1,210.566..9,230.939 rows=1,097,641 loops=1)

13. 0.011 0.122 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.120..0.122 rows=1 loops=1)

14.          

Initplan (for Result)

15. 0.056 0.111 ↑ 1.0 1 1

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

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

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

17. 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
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,057.431 8,309.500 ↓ 1.0 1,097,641 1

Hash Left Join (cost=65,332.45..372,370.79 rows=1,096,562 width=417) (actual time=1,210.441..8,309.500 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
20. 1,110.020 7,252.054 ↓ 1.0 1,097,641 1

Hash Join (cost=65,331.29..367,748.53 rows=1,096,562 width=417) (actual time=1,210.415..7,252.054 rows=1,097,641 loops=1)

  • Hash Cond: (t.projectid = p.id)
21. 988.470 6,132.789 ↓ 1.0 1,097,641 1

Hash Left Join (cost=64,935.78..364,473.32 rows=1,096,562 width=413) (actual time=1,201.121..6,132.789 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
22. 1,699.141 3,943.325 ↓ 1.0 1,097,641 1

Merge Join (cost=4.66..179,927.72 rows=1,096,562 width=373) (actual time=0.041..3,943.325 rows=1,097,641 loops=1)

  • Merge Cond: (t.id = tdh.taskid)
23. 1,107.527 1,107.527 ↑ 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=275) (actual time=0.014..1,107.527 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
24. 1,136.657 1,136.657 ↓ 1.0 1,097,642 1

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.43..104,241.45 rows=1,096,562 width=98) (actual time=0.022..1,136.657 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
25. 624.216 1,200.994 ↑ 1.0 1,097,648 1

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,092kB
26. 576.778 576.778 ↑ 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..576.778 rows=1,097,648 loops=1)

27. 4.763 9.245 ↑ 1.0 10,008 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 480kB
28. 4.482 4.482 ↑ 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.018..4.482 rows=10,008 loops=1)

  • Heap Fetches: 0
29. 0.006 0.015 ↑ 1.0 7 1

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

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

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

31. 0.000 55.712 ↑ 18.0 1 1

Hash (cost=23,187.19..23,187.19 rows=18 width=8) (actual time=55.712..55.712 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 29.561 55.776 ↑ 18.0 1 1

Gather Merge (cost=1,001.29..23,187.19 rows=18 width=8) (actual time=55.701..55.776 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 0.002 26.215 ↓ 0.0 0 3 / 3

Nested Loop Semi Join (cost=1.27..22,185.08 rows=8 width=8) (actual time=26.213..26.215 rows=0 loops=3)

34. 0.004 26.201 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.85..22,108.26 rows=172 width=12) (actual time=26.199..26.201 rows=0 loops=3)

35. 26.192 26.192 ↓ 0.0 0 3 / 3

Parallel Index Only Scan using idx_test2 on taskdenormalizedhierarchy tdh_1 (cost=0.43..20,702.36 rows=172 width=4) (actual time=26.191..26.192 rows=0 loops=3)

  • Index Cond: (taskownerid = 2)
  • Heap Fetches: 0
36. 0.005 0.005 ↑ 1.0 1 1 / 3

Index Scan using task_pkey on task task3 (cost=0.43..8.17 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)

  • Index Cond: (id = tdh_1.taskid)
37. 0.012 0.012 ↑ 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.035..0.036 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)
38. 0.006 0.017 ↑ 7.0 1 1

Materialize (cost=0.00..1.10 rows=7 width=4) (actual time=0.016..0.017 rows=1 loops=1)

39. 0.011 0.011 ↑ 7.0 1 1

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

40. 0.007 0.030 ↓ 7.0 7 1

Materialize (cost=0.00..1.62 rows=1 width=12) (actual time=0.018..0.030 rows=7 loops=1)

41. 0.023 0.023 ↓ 7.0 7 1

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

  • Filter: (variablecurrencyid = $2)
  • Rows Removed by Filter: 42
42. 0.022 0.022 ↑ 1.0 1 1

Index Scan using project_pkey on project project6 (cost=0.29..0.31 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (id = t.projectid)
  • Filter: (projectstatuslabelid = 'c721a296-1f22-426d-84dd-bc1f0467162a'::uuid)
43.          

SubPlan (for Result)

44. 0.022 0.022 ↑ 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.022 rows=1 loops=1)

  • Index Cond: (id = t.projectid)
45. 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 : 4.739 ms
Execution time : 10,709.363 ms