explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Eo9r

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 11,618.407 ↑ 222.0 1 1

Limit (cost=402,661.20..406,350.28 rows=222 width=3,351) (actual time=11,618.404..11,618.407 rows=1 loops=1)

  • Buffers: shared hit=2,060,605 read=31,401, temp read=1,094 written=8,413
2.          

Initplan (for Limit)

3. 0.006 0.006 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
4. 0.010 11,618.404 ↑ 222.0 1 1

Result (cost=402,660.19..406,349.27 rows=222 width=3,351) (actual time=11,618.402..11,618.404 rows=1 loops=1)

  • Buffers: shared hit=2,060,605 read=31,401, temp read=1,094 written=8,413
5. 0.010 11,618.370 ↑ 222.0 1 1

Sort (cost=402,660.19..402,660.74 rows=222 width=2,319) (actual time=11,618.369..11,618.370 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,060,599 read=31,401, temp read=1,094 written=8,413
6. 0.006 11,618.360 ↑ 222.0 1 1

Nested Loop (cost=118,269.95..402,651.53 rows=222 width=2,319) (actual time=11,592.344..11,618.360 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
  • Buffers: shared hit=2,060,599 read=31,401, temp read=1,094 written=8,413
7. 0.007 11,618.335 ↑ 222.0 1 1

Nested Loop Left Join (cost=118,269.67..402,580.66 rows=222 width=409) (actual time=11,592.321..11,618.335 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
  • Buffers: shared hit=2,060,597 read=31,400, temp read=1,094 written=8,413
8. 0.005 11,618.296 ↑ 222.0 1 1

Nested Loop (cost=118,269.67..402,574.60 rows=222 width=417) (actual time=11,592.283..11,618.296 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
  • Buffers: shared hit=2,060,596 read=31,399, temp read=1,094 written=8,413
9. 493.356 11,618.280 ↑ 222.0 1 1

Hash Semi Join (cost=118,269.67..402,552.72 rows=222 width=421) (actual time=11,592.268..11,618.280 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = task3.id)
  • Buffers: shared hit=2,060,595 read=31,399, temp read=1,094 written=8,413
10. 950.080 10,002.105 ↑ 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.482..10,002.105 rows=1,097,641 loops=1)

  • Buffers: shared hit=2,060,425 read=4,867, temp read=1,094 written=8,413
11. 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)

  • Buffers: shared hit=1
12. 940.622 9,052.014 ↑ 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.470..9,052.014 rows=1,097,641 loops=1)

  • Buffers: shared hit=2,060,424 read=4,867, temp read=1,094 written=8,413
13. 0.011 0.121 ↑ 1.0 1 1

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

  • Buffers: shared hit=3
14.          

Initplan (for Result)

15. 0.059 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)
  • Buffers: shared hit=3
16. 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)

  • Buffers: shared hit=2
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
  • Buffers: shared hit=1
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)

  • Buffers: shared hit=1
19. 1,036.546 8,111.271 ↑ 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,203.347..8,111.271 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
  • Buffers: shared hit=2,060,421 read=4,867, temp read=1,094 written=8,413
20. 1,099.058 7,074.709 ↑ 1.0 1,097,641 1

Hash Join (cost=65,329.59..320,149.17 rows=1,097,648 width=417) (actual time=1,203.314..7,074.709 rows=1,097,641 loops=1)

  • Hash Cond: (t.projectid = p.id)
  • Buffers: shared hit=2,060,420 read=4,867, temp read=1,094 written=8,413
21. 994.305 5,966.479 ↑ 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,194.115..5,966.479 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
  • Buffers: shared hit=2,060,390 read=4,867, temp read=1,094 written=8,413
22. 1,655.260 3,778.182 ↑ 1.0 1,097,641 1

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

  • Merge Cond: (t.id = tdh.taskid)
  • Buffers: shared hit=2,029,804 read=4,867
23. 1,069.510 1,069.510 ↑ 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.016..1,069.510 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
  • Buffers: shared hit=972,174 read=2,434
24. 1,053.412 1,053.412 ↑ 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.012..1,053.412 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,057,630 read=2,433
25. 623.738 1,193.992 ↑ 1.0 1,097,648 1

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

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,092kB
  • Buffers: shared hit=30,586, temp written=8,378
26. 570.254 570.254 ↑ 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..570.254 rows=1,097,648 loops=1)

  • Buffers: shared hit=30,586
27. 4.714 9.172 ↑ 1.0 10,008 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 480kB
  • Buffers: shared hit=30
28. 4.458 4.458 ↑ 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.021..4.458 rows=10,008 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=30
29. 0.005 0.016 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
30. 0.011 0.011 ↑ 1.0 7 1

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

  • Buffers: shared hit=1
31. 0.000 1,122.819 ↑ 222.0 1 1

Hash (cost=52,931.86..52,931.86 rows=222 width=8) (actual time=1,122.819..1,122.819 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=170 read=26,532
32. 7.022 1,122.883 ↑ 222.0 1 1

Gather (cost=1,000.84..52,931.86 rows=222 width=8) (actual time=0.915..1,122.883 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=170 read=26,532
33. 0.002 1,115.861 ↓ 0.0 0 3 / 3

Nested Loop Semi Join (cost=0.84..51,909.66 rows=92 width=8) (actual time=742.231..1,115.861 rows=0 loops=3)

  • Buffers: shared hit=170 read=26,532
34. 0.003 1,115.854 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.43..50,977.09 rows=2,088 width=12) (actual time=742.225..1,115.854 rows=0 loops=3)

  • Buffers: shared hit=166 read=26,532
35. 1,115.849 1,115.849 ↓ 0.0 0 3 / 3

Parallel Seq Scan on taskkeyvalue taskkeyvalue4 (cost=0.00..37,766.56 rows=2,088 width=4) (actual time=742.220..1,115.849 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
  • Buffers: shared hit=162 read=26,532
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)
  • Buffers: shared hit=4
37. 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)
  • Buffers: shared hit=4
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)

  • Buffers: shared hit=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.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
40. 0.008 0.032 ↓ 7.0 7 1

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

  • Buffers: shared hit=1 read=1
41. 0.024 0.024 ↓ 7.0 7 1

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

  • Filter: (variablecurrencyid = $2)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=1 read=1
42. 0.019 0.019 ↑ 1.0 1 1

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

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

SubPlan (for Result)

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

  • Index Cond: (id = t.projectid)
  • Buffers: shared hit=3
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)
  • Buffers: shared hit=3
Planning time : 4.808 ms
Execution time : 11,618.754 ms