explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Lw5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 12,302.467 ↑ 221.0 1 1

Limit (cost=402,621.50..406,293.97 rows=221 width=3,351) (actual time=12,302.464..12,302.467 rows=1 loops=1)

  • Buffers: shared hit=2,059,281 read=32,725, 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.010 12,302.464 ↑ 221.0 1 1

Result (cost=402,620.49..406,292.96 rows=221 width=3,351) (actual time=12,302.462..12,302.464 rows=1 loops=1)

  • Buffers: shared hit=2,059,281 read=32,725, temp read=1,094 written=8,413
5. 0.011 12,302.428 ↑ 221.0 1 1

Sort (cost=402,620.49..402,621.04 rows=221 width=2,319) (actual time=12,302.427..12,302.428 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,059,275 read=32,725, temp read=1,094 written=8,413
6. 0.005 12,302.417 ↑ 221.0 1 1

Nested Loop (cost=118,230.76..402,611.88 rows=221 width=2,319) (actual time=12,275.076..12,302.417 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
  • Buffers: shared hit=2,059,275 read=32,725, temp read=1,094 written=8,413
7. 0.006 12,302.400 ↑ 221.0 1 1

Nested Loop Left Join (cost=118,230.47..402,541.32 rows=221 width=409) (actual time=12,275.060..12,302.400 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,272 read=32,725, temp read=1,094 written=8,413
8. 0.005 12,302.362 ↑ 221.0 1 1

Nested Loop (cost=118,230.47..402,535.29 rows=221 width=417) (actual time=12,275.022..12,302.362 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
  • Buffers: shared hit=2,059,271 read=32,724, temp read=1,094 written=8,413
9. 500.685 12,302.345 ↑ 221.0 1 1

Hash Semi Join (cost=118,230.47..402,513.52 rows=221 width=421) (actual time=12,275.007..12,302.345 rows=1 loops=1)

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

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

  • Buffers: shared hit=2,059,260 read=6,032, 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. 938.883 9,046.348 ↑ 1.0 1,097,641 1

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

  • Buffers: shared hit=2,059,259 read=6,032, temp read=1,094 written=8,413
13. 0.011 0.117 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.115..0.117 rows=1 loops=1)

  • Buffers: shared hit=3
14.          

Initplan (for Result)

15. 0.056 0.106 ↑ 1.0 1 1

Hash Join (cost=1.02..4.28 rows=1 width=15) (actual time=0.099..0.106 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.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
  • 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.426 8,107.348 ↑ 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,190.699..8,107.348 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
  • Buffers: shared hit=2,059,256 read=6,032, temp read=1,094 written=8,413
20. 1,092.557 7,081.906 ↑ 1.0 1,097,641 1

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

  • Hash Cond: (t.projectid = p.id)
  • Buffers: shared hit=2,059,255 read=6,032, temp read=1,094 written=8,413
21. 1,004.589 5,980.017 ↑ 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,181.307..5,980.017 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
  • Buffers: shared hit=2,059,250 read=6,007, temp read=1,094 written=8,413
22. 1,654.303 3,794.256 ↑ 1.0 1,097,641 1

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

  • Merge Cond: (t.id = tdh.taskid)
  • Buffers: shared hit=2,028,664 read=6,007
23. 1,074.535 1,074.535 ↑ 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.015..1,074.535 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
  • Buffers: shared hit=971,602 read=3,006
24. 1,065.418 1,065.418 ↑ 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,065.418 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,057,062 read=3,001
25. 614.924 1,181.172 ↑ 1.0 1,097,648 1

Hash (cost=41,562.50..41,562.50 rows=1,097,650 width=44) (actual time=1,181.172..1,181.172 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. 566.248 566.248 ↑ 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..566.248 rows=1,097,648 loops=1)

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

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

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

  • Heap Fetches: 0
  • Buffers: shared hit=5 read=25
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,820.515 ↑ 221.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=10 read=26,692
32. 10.001 1,820.580 ↑ 221.0 1 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=10 read=26,692
33. 0.002 1,810.579 ↓ 0.0 0 3 / 3

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

  • Buffers: shared hit=10 read=26,692
34. 0.002 1,810.572 ↓ 0.0 0 3 / 3

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

  • Buffers: shared hit=6 read=26,692
35. 1,810.567 1,810.567 ↓ 0.0 0 3 / 3

Parallel Seq Scan on taskkeyvalue taskkeyvalue4 (cost=0.00..37,766.56 rows=2,081 width=4) (actual time=1,204.779..1,810.567 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=2 read=26,692
36. 0.003 0.003 ↑ 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.008 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.005 0.012 ↑ 7.0 1 1

Materialize (cost=0.00..1.10 rows=7 width=4) (actual time=0.011..0.012 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.019..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.017..0.024 rows=7 loops=1)

  • Filter: (variablecurrencyid = $2)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=1 read=1
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.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)
  • 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.670 ms
Execution time : 12,302.814 ms