explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e1xw

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 11,530.689 ↑ 221.0 1 1

Limit (cost=402,621.50..406,293.97 rows=221 width=3,351) (actual time=11,530.686..11,530.689 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 11,530.687 ↑ 221.0 1 1

Result (cost=402,620.49..406,292.96 rows=221 width=3,351) (actual time=11,530.685..11,530.687 rows=1 loops=1)

5. 0.012 11,530.651 ↑ 221.0 1 1

Sort (cost=402,620.49..402,621.04 rows=221 width=2,319) (actual time=11,530.650..11,530.651 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
6. 0.005 11,530.639 ↑ 221.0 1 1

Nested Loop (cost=118,230.76..402,611.88 rows=221 width=2,319) (actual time=11,503.050..11,530.639 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
7. 0.007 11,530.605 ↑ 221.0 1 1

Nested Loop Left Join (cost=118,230.47..402,541.32 rows=221 width=409) (actual time=11,503.016..11,530.605 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.003 11,530.564 ↑ 221.0 1 1

Nested Loop (cost=118,230.47..402,535.29 rows=221 width=417) (actual time=11,502.977..11,530.564 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
9. 472.779 11,530.550 ↑ 221.0 1 1

Hash Semi Join (cost=118,230.47..402,513.52 rows=221 width=421) (actual time=11,502.963..11,530.550 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = task3.id)
10. 917.321 9,793.451 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,335.03..346,734.29 rows=1,097,648 width=425) (actual time=1,203.436..9,793.451 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. 917.251 8,876.120 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,335.03..335,756.80 rows=1,097,648 width=421) (actual time=1,203.425..8,876.120 rows=1,097,641 loops=1)

13. 0.013 1.008 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=1.007..1.008 rows=1 loops=1)

14.          

Initplan (for Result)

15. 0.054 0.995 ↑ 1.0 1 1

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

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

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

17. 0.005 0.011 ↑ 1.0 1 1

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

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

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

19. 1,005.574 7,957.861 ↑ 1.0 1,097,641 1

Hash Left Join (cost=65,330.75..324,776.01 rows=1,097,648 width=417) (actual time=1,202.413..7,957.861 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
20. 1,051.803 6,952.272 ↑ 1.0 1,097,641 1

Hash Join (cost=65,329.59..320,149.17 rows=1,097,648 width=417) (actual time=1,202.383..6,952.272 rows=1,097,641 loops=1)

  • Hash Cond: (t.projectid = p.id)
21. 953.419 5,888.599 ↑ 1.0 1,097,641 1

Hash Left Join (cost=64,934.09..316,871.12 rows=1,097,648 width=413) (actual time=1,190.485..5,888.599 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
22. 1,619.237 3,744.820 ↑ 1.0 1,097,641 1

Merge Join (cost=2.96..132,216.67 rows=1,097,648 width=373) (actual time=0.029..3,744.820 rows=1,097,641 loops=1)

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

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
24. 1,053.757 1,053.757 ↑ 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=0.010..1,053.757 rows=1,097,642 loops=1)

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

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

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

27. 5.083 11.870 ↑ 1.0 10,008 1

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

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

  • Heap Fetches: 0
29. 0.005 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.010 0.010 ↑ 1.0 7 1

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

31. 0.000 1,264.320 ↑ 221.0 1 1

Hash (cost=52,892.68..52,892.68 rows=221 width=8) (actual time=1,264.320..1,264.320 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 16.666 1,264.405 ↑ 221.0 1 1

Gather (cost=1,000.84..52,892.68 rows=221 width=8) (actual time=0.920..1,264.405 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 0.002 1,247.739 ↓ 0.0 0 3 / 3

Nested Loop Semi Join (cost=0.84..51,870.58 rows=92 width=8) (actual time=826.607..1,247.739 rows=0 loops=3)

34. 0.003 1,247.729 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.43..50,941.13 rows=2,081 width=12) (actual time=826.598..1,247.729 rows=0 loops=3)

35. 1,247.724 1,247.724 ↓ 0.0 0 3 / 3

Parallel Seq Scan on taskkeyvalue taskkeyvalue4 (cost=0.00..37,766.56 rows=2,081 width=4) (actual time=826.593..1,247.724 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
36. 0.002 0.002 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (id = taskkeyvalue4.taskid)
37. 0.008 0.008 ↑ 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.023..0.024 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.004 0.011 ↑ 7.0 1 1

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

39. 0.007 0.007 ↑ 7.0 1 1

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

40. 0.008 0.034 ↓ 7.0 7 1

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

41. 0.026 0.026 ↓ 7.0 7 1

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

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

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

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

SubPlan (for Result)

44. 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.019..0.021 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 : 9.648 ms
Execution time : 11,531.039 ms