explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dZg4

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

Limit (cost=420,385.34..420,684.46 rows=18 width=3,351) (actual time=10,371.002..10,371.005 rows=1 loops=1)

2.          

CTE x

3. 39.067 49.606 ↑ 18.0 1 1

Gather (cost=1,001.27..23,186.88 rows=18 width=4) (actual time=32.783..49.606 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.002 10.539 ↓ 0.0 0 3 / 3

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

5. 0.003 10.526 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.85..22,108.26 rows=172 width=8) (actual time=10.525..10.526 rows=0 loops=3)

6. 10.519 10.519 ↓ 0.0 0 3 / 3

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

  • Index Cond: (taskownerid = 2)
  • Heap Fetches: 0
7. 0.004 0.004 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: (id = t_1.taskid)
8. 0.011 0.011 ↑ 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.031..0.032 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.010 10,371.002 ↑ 18.0 1 1

Result (cost=397,197.45..397,496.57 rows=18 width=3,351) (actual time=10,371.000..10,371.002 rows=1 loops=1)

12. 0.012 10,370.960 ↑ 18.0 1 1

Sort (cost=397,197.45..397,197.50 rows=18 width=2,319) (actual time=10,370.959..10,370.960 rows=1 loops=1)

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

Nested Loop (cost=65,337.60..397,197.08 rows=18 width=2,319) (actual time=10,343.183..10,370.948 rows=1 loops=1)

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

Nested Loop Left Join (cost=65,337.32..397,191.33 rows=18 width=409) (actual time=10,343.145..10,370.908 rows=1 loops=1)

  • Join Filter: ((exchangerate.enddate >= ((timezone(($7)::text, now()))::date)) AND (exchangerate.effectivedate <= ((timezone(($7)::text, now()))::date)) AND (exchangerate.fixedcurrencyid = currencyinfo.id))
  • Rows Removed by Join Filter: 7
15. 0.005 10,370.859 ↑ 18.0 1 1

Nested Loop (cost=65,337.32..397,189.35 rows=18 width=417) (actual time=10,343.097..10,370.859 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
16. 475.309 10,370.815 ↑ 18.0 1 1

Hash Semi Join (cost=65,337.32..397,186.61 rows=18 width=421) (actual time=10,343.054..10,370.815 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = x.taskid)
17. 910.456 9,845.974 ↓ 1.0 1,097,641 1

Nested Loop (cost=65,336.73..394,307.35 rows=1,096,562 width=425) (actual time=1,183.084..9,845.974 rows=1,097,641 loops=1)

18. 0.011 0.011 ↑ 1.0 1 1

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

19. 900.593 8,935.507 ↓ 1.0 1,097,641 1

Nested Loop (cost=65,336.73..383,340.72 rows=1,096,562 width=421) (actual time=1,183.072..8,935.507 rows=1,097,641 loops=1)

20. 0.010 0.120 ↑ 1.0 1 1

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

21.          

Initplan (for Result)

22. 0.052 0.110 ↑ 1.0 1 1

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

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

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

24. 0.003 0.007 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.007..0.007 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,018.377 8,034.794 ↓ 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,182.949..8,034.794 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
27. 1,068.973 7,016.400 ↓ 1.0 1,097,641 1

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

  • Hash Cond: (t.projectid = p.id)
28. 969.370 5,938.252 ↓ 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,173.699..5,938.252 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
29. 1,612.173 3,795.310 ↓ 1.0 1,097,641 1

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

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

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
31. 1,121.239 1,121.239 ↓ 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.021..1,121.239 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
32. 605.852 1,173.572 ↑ 1.0 1,097,648 1

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

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

34. 4.610 9.175 ↑ 1.0 10,008 1

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

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

  • Heap Fetches: 0
36. 0.007 0.017 ↑ 1.0 7 1

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

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

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

38. 0.004 49.532 ↑ 18.0 1 1

Hash (cost=0.36..0.36 rows=18 width=4) (actual time=49.532..49.532 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 49.528 49.528 ↑ 18.0 1 1

CTE Scan on x (cost=0.00..0.36 rows=18 width=4) (actual time=32.788..49.528 rows=1 loops=1)

40. 0.015 0.039 ↑ 7.0 1 1

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

41. 0.024 0.024 ↑ 7.0 1 1

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

42. 0.009 0.042 ↓ 7.0 7 1

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

43. 0.033 0.033 ↓ 7.0 7 1

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

  • Filter: (variablecurrencyid = $6)
  • Rows Removed by Filter: 42
44. 0.034 0.034 ↑ 1.0 1 1

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

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

SubPlan (for Result)

46. 0.028 0.028 ↑ 1.0 1 1

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

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