explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pLCk

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

Limit (cost=402,621.50..406,293.97 rows=221 width=3,351) (actual time=11,572.008..11,572.011 rows=1 loops=1)

  • Buffers: shared hit=2,059,448 read=32,558, 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.004..0.006 rows=1 loops=1)

  • Buffers: shared hit=1
4. 0.013 11,572.008 ↑ 221.0 1 1

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

  • Buffers: shared hit=2,059,448 read=32,558, temp read=1,094 written=8,413
5. 0.011 11,571.950 ↑ 221.0 1 1

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

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,059,442 read=32,558, temp read=1,094 written=8,413
6. 0.006 11,571.939 ↑ 221.0 1 1

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

  • Join Filter: (p.id = project6.id)
  • Buffers: shared hit=2,059,442 read=32,558, temp read=1,094 written=8,413
7. 0.007 11,571.921 ↑ 221.0 1 1

Nested Loop Left Join (cost=118,230.47..402,541.32 rows=221 width=409) (actual time=11,544.736..11,571.921 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,059,439 read=32,558, temp read=1,094 written=8,413
8. 0.005 11,571.885 ↑ 221.0 1 1

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

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
  • Buffers: shared hit=2,059,437 read=32,558, temp read=1,094 written=8,413
9. 490.302 11,571.866 ↑ 221.0 1 1

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

  • Hash Cond: (tdh.taskid = task3.id)
  • Buffers: shared hit=2,059,436 read=32,558, temp read=1,094 written=8,413
10. 937.549 9,943.965 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,335.03..346,734.29 rows=1,097,648 width=425) (actual time=1,196.221..9,943.965 rows=1,097,641 loops=1)

  • Buffers: shared hit=2,059,397 read=5,895, 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.007..0.011 rows=1 loops=1)

  • Buffers: shared hit=1
12. 927.754 9,006.405 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,335.03..335,756.80 rows=1,097,648 width=421) (actual time=1,196.210..9,006.405 rows=1,097,641 loops=1)

  • Buffers: shared hit=2,059,396 read=5,895, temp read=1,094 written=8,413
13. 0.013 0.137 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.136..0.137 rows=1 loops=1)

  • Buffers: shared hit=1 read=2
14.          

Initplan (for Result)

15. 0.056 0.124 ↑ 1.0 1 1

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

  • Hash Cond: (timezone.id = systeminformation_1.systemtimezoneid)
  • Buffers: shared hit=1 read=2
16. 0.060 0.060 ↑ 1.0 91 1

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

  • Buffers: shared read=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.003..0.004 rows=1 loops=1)

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

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
  • Buffers: shared hit=2,059,395 read=5,893, temp read=1,094 written=8,413
20. 1,106.049 7,052.871 ↑ 1.0 1,097,641 1

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

  • Hash Cond: (t.projectid = p.id)
  • Buffers: shared hit=2,059,394 read=5,893, temp read=1,094 written=8,413
21. 983.142 5,937.563 ↑ 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,186.756..5,937.563 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
  • Buffers: shared hit=2,059,389 read=5,868, temp read=1,094 written=8,413
22. 1,649.250 3,767.783 ↑ 1.0 1,097,641 1

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

  • Merge Cond: (t.id = tdh.taskid)
  • Buffers: shared hit=2,028,825 read=5,846
23. 1,069.014 1,069.014 ↑ 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,069.014 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
  • Buffers: shared hit=971,685 read=2,923
24. 1,049.519 1,049.519 ↑ 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.011..1,049.519 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,057,140 read=2,923
25. 620.881 1,186.638 ↑ 1.0 1,097,648 1

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

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

  • Buffers: shared hit=30,564 read=22
27. 4.748 9.259 ↑ 1.0 10,008 1

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

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

  • Heap Fetches: 0
  • Buffers: shared hit=5 read=25
29. 0.006 0.015 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
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)

  • Buffers: shared hit=1
31. 0.000 1,137.599 ↑ 221.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=39 read=26,663
32. 41.127 1,137.669 ↑ 221.0 1 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=39 read=26,663
33. 0.002 1,096.542 ↓ 0.0 0 3 / 3

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

  • Buffers: shared hit=39 read=26,663
34. 0.003 1,096.380 ↓ 0.0 0 3 / 3

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

  • Buffers: shared hit=38 read=26,660
35. 1,096.375 1,096.375 ↓ 0.0 0 3 / 3

Parallel Seq Scan on taskkeyvalue taskkeyvalue4 (cost=0.00..37,766.56 rows=2,081 width=4) (actual time=717.727..1,096.375 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=34 read=26,660
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.160 0.160 ↑ 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.479..0.480 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=1 read=3
38. 0.006 0.014 ↑ 7.0 1 1

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

  • Buffers: shared hit=1
39. 0.008 0.008 ↑ 7.0 1 1

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

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

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

  • Buffers: shared hit=2
41. 0.021 0.021 ↓ 7.0 7 1

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

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

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

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

SubPlan (for Result)

44. 0.041 0.041 ↑ 1.0 1 1

Index Scan using project_pkey on project project1 (cost=0.29..8.30 rows=1 width=516) (actual time=0.039..0.041 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.004..0.004 rows=1 loops=1)

  • Index Cond: (id = t.projectid)
  • Buffers: shared hit=3
Planning time : 26.057 ms
Execution time : 11,572.389 ms