explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bNmN

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 14,615.146 ↑ 221.0 1 1

Limit (cost=401,994.78..405,667.25 rows=221 width=3,351) (actual time=14,615.142..14,615.146 rows=1 loops=1)

  • Buffers: shared hit=6,401,674 read=65,360, temp read=1,094 written=8,413
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)

  • Buffers: shared hit=1
4. 0.013 14,615.143 ↑ 221.0 1 1

Result (cost=401,993.77..405,666.24 rows=221 width=3,351) (actual time=14,615.140..14,615.143 rows=1 loops=1)

  • Buffers: shared hit=6,401,674 read=65,360, temp read=1,094 written=8,413
5. 0.010 14,615.103 ↑ 221.0 1 1

Sort (cost=401,993.77..401,994.32 rows=221 width=2,319) (actual time=14,615.102..14,615.103 rows=1 loops=1)

  • Sort Key: t.timeentrystartdate, t.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6,401,668 read=65,360, temp read=1,094 written=8,413
6. 0.006 14,615.093 ↑ 221.0 1 1

Nested Loop (cost=117,604.04..401,985.16 rows=221 width=2,319) (actual time=14,585.957..14,615.093 rows=1 loops=1)

  • Join Filter: (p.id = project6.id)
  • Buffers: shared hit=6,401,668 read=65,360, temp read=1,094 written=8,413
7. 0.007 14,615.061 ↑ 221.0 1 1

Nested Loop Left Join (cost=117,603.75..401,914.60 rows=221 width=409) (actual time=14,585.927..14,615.061 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=6,401,668 read=65,357, temp read=1,094 written=8,413
8. 0.004 14,615.023 ↑ 221.0 1 1

Nested Loop (cost=117,603.75..401,908.57 rows=221 width=417) (actual time=14,585.890..14,615.023 rows=1 loops=1)

  • Join Filter: (sysinfo.basecurrencyid = currencyinfo2.id)
  • Buffers: shared hit=6,401,667 read=65,356, temp read=1,094 written=8,413
9. 493.851 14,615.003 ↑ 221.0 1 1

Hash Semi Join (cost=117,603.75..401,886.80 rows=221 width=421) (actual time=14,585.871..14,615.003 rows=1 loops=1)

  • Hash Cond: (tdh.taskid = task3.id)
  • Buffers: shared hit=6,401,667 read=65,355, temp read=1,094 written=8,413
10. 934.769 10,660.619 ↑ 1.0 1,097,641 1

Nested Loop (cost=65,335.03..346,734.29 rows=1,097,648 width=425) (actual time=1,291.585..10,660.619 rows=1,097,641 loops=1)

  • Buffers: shared hit=2,012,646 read=52,646, temp read=1,094 written=8,413
11. 0.012 0.012 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
12. 931.560 9,725.838 ↑ 1.0 1,097,641 1

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

  • Buffers: shared hit=2,012,645 read=52,646, temp read=1,094 written=8,413
13. 0.011 0.129 ↑ 1.0 1 1

Result (cost=4.28..4.30 rows=1 width=4) (actual time=0.127..0.129 rows=1 loops=1)

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

Initplan (for Result)

15. 0.057 0.118 ↑ 1.0 1 1

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

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

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

  • Buffers: shared read=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,030.677 8,794.149 ↑ 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,291.441..8,794.149 rows=1,097,641 loops=1)

  • Hash Cond: (t.estimatedcostcurrencyid = currencyinfo.id)
  • Buffers: shared hit=2,012,644 read=52,644, temp read=1,094 written=8,413
20. 1,130.290 7,763.448 ↑ 1.0 1,097,641 1

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

  • Hash Cond: (t.projectid = p.id)
  • Buffers: shared hit=2,012,644 read=52,643, temp read=1,094 written=8,413
21. 986.969 6,623.837 ↑ 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,282.052..6,623.837 rows=1,097,641 loops=1)

  • Hash Cond: (t.parentid = parent.id)
  • Buffers: shared hit=2,012,639 read=52,618, temp read=1,094 written=8,413
22. 1,641.969 4,354.944 ↑ 1.0 1,097,641 1

Merge Join (cost=2.96..132,216.67 rows=1,097,648 width=373) (actual time=0.035..4,354.944 rows=1,097,641 loops=1)

  • Merge Cond: (t.id = tdh.taskid)
  • Buffers: shared hit=1,986,382 read=48,289
23. 1,112.806 1,112.806 ↑ 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.017..1,112.806 rows=1,097,647 loops=1)

  • Filter: istimeentryallowed
  • Rows Removed by Filter: 1
  • Buffers: shared hit=955,070 read=19,538
24. 1,600.169 1,600.169 ↑ 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.013..1,600.169 rows=1,097,642 loops=1)

  • Filter: effectivelyenabled
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,031,312 read=28,751
25. 638.966 1,281.924 ↑ 1.0 1,097,648 1

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

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

  • Buffers: shared hit=26,257 read=4,329
27. 4.788 9.321 ↑ 1.0 10,008 1

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

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

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

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

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

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

  • Buffers: shared read=1
31. 0.000 3,460.533 ↑ 221.0 1 1

Hash (cost=52,265.96..52,265.96 rows=221 width=8) (actual time=3,460.533..3,460.533 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4,389,021 read=12,709
32. 9.075 3,460.600 ↑ 221.0 1 1

Gather (cost=3,000.32..52,265.96 rows=221 width=8) (actual time=3,458.515..3,460.600 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=4,389,021 read=12,709
33. 587.992 3,451.525 ↓ 0.0 0 3 / 3

Nested Loop (cost=2,000.32..51,243.86 rows=92 width=8) (actual time=3,450.948..3,451.525 rows=0 loops=3)

  • Buffers: shared hit=4,389,021 read=12,709
34. 619.538 1,053.076 ↓ 17.9 362,091 3 / 3

Hash Join (cost=1,999.77..38,585.14 rows=20,250 width=4) (actual time=141.983..1,053.076 rows=362,091 loops=3)

  • Hash Cond: (task3.projectid = projectkeyvalue5.projectid)
  • Buffers: shared hit=32,575 read=1,014
35. 291.583 291.583 ↑ 1.3 365,883 3 / 3

Parallel Seq Scan on task task3 (cost=0.00..35,159.54 rows=457,354 width=8) (actual time=0.009..291.583 rows=365,883 loops=3)

  • Buffers: shared hit=30,570 read=16
36. 8.686 141.955 ↓ 199.3 9,963 3 / 3

Hash (cost=1,999.14..1,999.14 rows=50 width=4) (actual time=141.955..141.955 rows=9,963 loops=3)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 479kB
  • Buffers: shared hit=2,005 read=998
37. 17.751 133.269 ↓ 199.3 9,963 3 / 3

HashAggregate (cost=1,998.64..1,999.14 rows=50 width=4) (actual time=124.165..133.269 rows=9,963 loops=3)

  • Group Key: projectkeyvalue5.projectid
  • Buffers: shared hit=2,005 read=998
38. 115.518 115.518 ↓ 199.3 9,963 3 / 3

Seq Scan on projectkeyvalue projectkeyvalue5 (cost=0.00..1,998.52 rows=50 width=4) (actual time=0.302..115.518 rows=9,963 loops=3)

  • Filter: ((upper(keyuri) = 'URN:REPLICON:PROJECT-KEY-VALUE-KEY:PROJECT-MANAGEMENT-TYPE'::text) AND (upper(uri) = 'URN:REPLICON:PROJECT-MANAGEMENT-TYPE:MANAGED'::text))
  • Rows Removed by Filter: 39,913
  • Buffers: shared hit=2,005 read=998
39. 1,810.457 1,810.457 ↓ 0.0 0 1,086,274 / 3

Index Only Scan using idx_test on taskkeyvalue taskkeyvalue4 (cost=0.55..0.62 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1,086,274)

  • Index Cond: (taskid = task3.id)
  • Filter: (upper(uri) = 'URN:REPLICON-TENANT:B802550F8FA040E28174C57923F30EE9:USER:2'::text)
  • Rows Removed by Filter: 1
  • Heap Fetches: 0
  • Buffers: shared hit=4,356,446 read=11,695
40. 0.006 0.016 ↑ 7.0 1 1

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

  • Buffers: shared read=1
41. 0.010 0.010 ↑ 7.0 1 1

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

  • Buffers: shared read=1
42. 0.008 0.031 ↓ 7.0 7 1

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

  • Buffers: shared hit=1 read=1
43. 0.023 0.023 ↓ 7.0 7 1

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

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

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

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

SubPlan (for Result)

46. 0.023 0.023 ↑ 1.0 1 1

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

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