explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CTSw : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #DOdM; plan #KAL0; plan #BvFq; plan #3pUm; plan #gFED

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 3,998.000 ↑ 1.0 1 1

Unique (cost=119,615.18..119,615.20 rows=1 width=69) (actual time=3,997.995..3,998.000 rows=1 loops=1)

2. 0.026 3,997.994 ↓ 3.0 3 1

Sort (cost=119,615.18..119,615.19 rows=1 width=69) (actual time=3,997.994..3,997.994 rows=3 loops=1)

  • Sort Key: i0."Tag", i0."RefId", n0."RefId", s."StatusId", d."Name
  • Sort Method: quicksort Memory: 25kB
3. 0.013 3,997.968 ↓ 3.0 3 1

Nested Loop (cost=1,009.99..119,615.17 rows=1 width=69) (actual time=3,997.715..3,997.968 rows=3 loops=1)

  • Join Filter: (s."TenantId" = d."TenantId")
4. 0.053 3,997.895 ↑ 1.0 3 1

Nested Loop (cost=1,009.71..119,614.26 rows=3 width=75) (actual time=3,997.670..3,997.895 rows=3 loops=1)

  • Join Filter: ((s."TenantId" = l0."TenantId") AND (l."LanguageId" = l0."Id"))
  • Rows Removed by Join Filter: 69
5. 0.036 0.036 ↑ 1.0 3 1

Index Scan using "Language_IX_TenantId" on "Language" l0 (cost=0.14..12.56 rows=3 width=16) (actual time=0.009..0.036 rows=3 loops=1)

  • Filter: ("LocalId" = 1,031)
  • Rows Removed by Filter: 21
6. 0.050 3,997.806 ↑ 2.5 24 3

Materialize (cost=1,009.58..119,598.65 rows=61 width=75) (actual time=1,332.549..1,332.602 rows=24 loops=3)

7. 0.406 3,997.756 ↑ 2.5 24 1

Merge Join (cost=1,009.58..119,598.34 rows=61 width=75) (actual time=3,997.640..3,997.756 rows=24 loops=1)

  • Merge Cond: (i1."Id" = i0."StatusId")
8. 1.356 3,996.906 ↑ 4.4 337 1

Nested Loop (cost=1,001.66..130,922.10 rows=1,467 width=48) (actual time=8.791..3,996.906 rows=337 loops=1)

  • Join Filter: ((s."StatusName")::text = (i1."Name")::text)
  • Rows Removed by Join Filter: 2,032
9. 0.060 0.060 ↑ 1.6 15 1

Index Only Scan using "InstrumentStatu_View_IDX" on "InstrumentationStatus" i1 (cost=0.14..12.50 rows=24 width=17) (actual time=0.010..0.060 rows=15 loops=1)

  • Heap Fetches: 15
10. 0.000 3,995.490 ↑ 2.7 158 15

Materialize (cost=1,001.52..130,756.59 rows=428 width=52) (actual time=0.578..266.366 rows=158 loops=15)

11. 570.400 4,040.612 ↑ 2.5 168 1

Gather (cost=1,001.52..130,754.45 rows=428 width=52) (actual time=8.661..4,040.612 rows=168 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,228.804 3,470.212 ↑ 3.2 56 3 / 3

Hash Join (cost=1.52..129,711.65 rows=178 width=52) (actual time=2,142.321..3,470.212 rows=56 loops=3)

  • Hash Cond: ((l."CommonId" = s."LanguageDescriptionId") AND (l."TenantId" = s."TenantId"))
13. 2,240.097 2,240.097 ↑ 1.3 2,767,101 3 / 3

Parallel Seq Scan on "LanguageDescription" l (cost=0.00..103,766.77 rows=3,458,877 width=32) (actual time=7.109..2,240.097 rows=2,767,101 loops=3)

14. 0.043 1.311 ↑ 1.0 21 3 / 3

Hash (cost=1.21..1.21 rows=21 width=36) (actual time=1.311..1.311 rows=21 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 1.268 1.268 ↑ 1.0 21 3 / 3

Seq Scan on "Statuses" s (cost=0.00..1.21 rows=21 width=36) (actual time=1.254..1.268 rows=21 loops=3)

16. 0.011 0.444 ↑ 1.0 1 1

Materialize (cost=7.91..7.92 rows=1 width=43) (actual time=0.440..0.444 rows=1 loops=1)

17. 0.015 0.433 ↑ 1.0 1 1

Sort (cost=7.91..7.92 rows=1 width=43) (actual time=0.432..0.433 rows=1 loops=1)

  • Sort Key: i0."StatusId
  • Sort Method: quicksort Memory: 25kB
18. 0.004 0.418 ↑ 1.0 1 1

Nested Loop (cost=5.08..7.90 rows=1 width=43) (actual time=0.356..0.418 rows=1 loops=1)

  • Join Filter: (n."Id" = n1."NodeId")
19. 0.014 0.391 ↑ 1.0 1 1

Nested Loop (cost=4.94..7.00 rows=1 width=59) (actual time=0.331..0.391 rows=1 loops=1)

20. 0.045 0.311 ↓ 6.0 6 1

Hash Join (cost=4.80..6.11 rows=1 width=40) (actual time=0.292..0.311 rows=6 loops=1)

  • Hash Cond: (i."NodeId" = n."Id")
21. 0.035 0.035 ↑ 1.0 22 1

Seq Scan on "InstrumentationNode" i (cost=0.00..1.22 rows=22 width=16) (actual time=0.025..0.035 rows=22 loops=1)

22. 0.010 0.231 ↓ 3.0 6 1

Hash (cost=4.78..4.78 rows=2 width=24) (actual time=0.231..0.231 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.038 0.221 ↓ 3.0 6 1

Hash Join (cost=1.36..4.78 rows=2 width=24) (actual time=0.132..0.221 rows=6 loops=1)

  • Hash Cond: (n."NodeTypeId" = n0."Id")
24. 0.127 0.127 ↓ 1.7 24 1

Seq Scan on "Node" n (cost=0.00..3.37 rows=14 width=24) (actual time=0.051..0.127 rows=24 loops=1)

  • Filter: (("TenantId" = 2) AND ("ProjectId" = 2))
  • Rows Removed by Filter: 67
25. 0.009 0.056 ↑ 1.0 3 1

Hash (cost=1.32..1.32 rows=3 width=16) (actual time=0.056..0.056 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.047 0.047 ↑ 1.0 3 1

Seq Scan on "NodeType" n0 (cost=0.00..1.32 rows=3 width=16) (actual time=0.026..0.047 rows=3 loops=1)

  • Filter: ("IsArea" = 1)
  • Rows Removed by Filter: 23
27. 0.066 0.066 ↓ 0.0 0 6

Index Scan using "Instrumentation_View_IDX" on "Instrumentation" i0 (cost=0.14..0.70 rows=1 width=35) (actual time=0.011..0.011 rows=0 loops=6)

  • Index Cond: ("Id" = i."InstrumentationId")
  • Filter: ("ParentId" = 0)
  • Rows Removed by Filter: 1
28. 0.023 0.023 ↑ 1.0 1 1

Index Only Scan using "NodeSpecification_IX_NodeId" on "NodeSpecification" n1 (cost=0.14..0.89 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: ("NodeId" = i."NodeId")
  • Heap Fetches: 1
29. 0.060 0.060 ↑ 1.0 1 3

Index Scan using "PK_public.Description" on "Description" d (cost=0.27..0.29 rows=1 width=38) (actual time=0.020..0.020 rows=1 loops=3)

  • Index Cond: ("Id" = l."DescriptionId")
  • Filter: (l."TenantId" = "TenantId")