explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pXHR : Optimization for: Optimization for: Optimization for: plan #HrkC4; plan #rBd2; plan #InBG

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 0.385 ↑ 1.0 1 1

Limit (cost=26.97..27.01 rows=1 width=255) (actual time=0.384..0.385 rows=1 loops=1)

2. 0.000 0.383 ↑ 1.0 1 1

Unique (cost=26.97..27.01 rows=1 width=255) (actual time=0.383..0.383 rows=1 loops=1)

3. 0.018 0.383 ↑ 1.0 1 1

Sort (cost=26.97..26.98 rows=1 width=255) (actual time=0.382..0.383 rows=1 loops=1)

  • Sort Key: n0."Name", n0."LanguageDescriptionId", n0."RefId", a."SerialNumber", n."Name", s."StatusName", ((s."StatusId")::integer), c."Name", a."StatusId", n."ParentId", a."TenantId", a2."Value", p."Name", p0."Value
  • Sort Method: quicksort Memory: 25kB
4. 0.003 0.365 ↑ 1.0 1 1

Nested Loop (cost=3.94..26.96 rows=1 width=255) (actual time=0.324..0.365 rows=1 loops=1)

  • Join Filter: (a1."NodeId" = n1."NodeId")
5. 0.003 0.349 ↑ 1.0 1 1

Nested Loop (cost=3.79..26.11 rows=1 width=243) (actual time=0.310..0.349 rows=1 loops=1)

  • Join Filter: (a."ProductId" = p0."ProductId")
6. 0.003 0.339 ↑ 1.0 1 1

Nested Loop (cost=3.65..25.77 rows=1 width=254) (actual time=0.301..0.339 rows=1 loops=1)

  • Join Filter: ((a0."Name")::text = (s."StatusName")::text)
  • Rows Removed by Join Filter: 6
7. 0.002 0.315 ↑ 1.0 1 1

Nested Loop (cost=3.65..24.42 rows=1 width=237) (actual time=0.283..0.315 rows=1 loops=1)

8. 0.003 0.271 ↑ 1.0 1 1

Nested Loop (cost=3.65..20.91 rows=1 width=189) (actual time=0.250..0.271 rows=1 loops=1)

9. 0.005 0.253 ↑ 1.0 1 1

Nested Loop (cost=3.51..20.22 rows=1 width=136) (actual time=0.232..0.253 rows=1 loops=1)

  • Join Filter: (a1."NodeId" = n."Id")
  • Rows Removed by Join Filter: 11
10. 0.000 0.217 ↑ 1.0 1 1

Nested Loop (cost=3.51..16.78 rows=1 width=100) (actual time=0.197..0.217 rows=1 loops=1)

11. 0.002 0.181 ↑ 1.0 1 1

Nested Loop (cost=3.51..14.69 rows=1 width=92) (actual time=0.170..0.181 rows=1 loops=1)

12. 0.003 0.169 ↑ 1.0 1 1

Nested Loop (cost=3.22..6.38 rows=1 width=89) (actual time=0.158..0.169 rows=1 loops=1)

13. 0.029 0.134 ↑ 1.0 1 1

Hash Join (cost=3.09..6.03 rows=1 width=83) (actual time=0.123..0.134 rows=1 loops=1)

  • Hash Cond: (p."Id" = a."ProductId")
14. 0.055 0.055 ↑ 1.0 74 1

Seq Scan on "Product" p (cost=0.00..2.74 rows=74 width=33) (actual time=0.044..0.055 rows=74 loops=1)

15. 0.007 0.050 ↑ 1.0 1 1

Hash (cost=3.08..3.08 rows=1 width=50) (actual time=0.050..0.050 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.043 0.043 ↑ 1.0 1 1

Seq Scan on "Asset" a (cost=0.00..3.08 rows=1 width=50) (actual time=0.042..0.043 rows=1 loops=1)

  • Filter: ("Id" = 71)
  • Rows Removed by Filter: 85
17. 0.032 0.032 ↑ 1.0 1 1

Index Only Scan using "Company_View_IDX" on "Company" c (cost=0.14..0.31 rows=1 width=22) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: ("Id" = p."ManufactureId")
  • Heap Fetches: 1
18. 0.010 0.010 ↑ 1.0 1 1

Index Scan using "AssetSts_View_IDX" on "AssetStatus" a0 (cost=0.29..8.31 rows=1 width=11) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ("Id" = a."StatusId")
  • Filter: ("TenantId" = 2)
19. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on "AssetNode" a1 (cost=0.00..2.08 rows=1 width=16) (actual time=0.026..0.036 rows=1 loops=1)

  • Filter: ("AssetId" = 71)
  • Rows Removed by Filter: 85
20. 0.031 0.031 ↑ 2.0 12 1

Seq Scan on "Node" n (cost=0.00..3.14 rows=24 width=36) (actual time=0.023..0.031 rows=12 loops=1)

  • Filter: ("ProjectId" = 2)
  • Rows Removed by Filter: 41
21. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using "NodeType_View_IDX" on "NodeType" n0 (cost=0.14..0.66 rows=1 width=69) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: ("Id" = n."NodeTypeId")
  • Heap Fetches: 1
22. 0.042 0.042 ↑ 1.0 1 1

Seq Scan on "AssetSpecification" a2 (cost=0.00..3.50 rows=1 width=56) (actual time=0.031..0.042 rows=1 loops=1)

  • Filter: (("AssetId" = 71) AND (strpos("Key", 'Revision'::text) > 0))
  • Rows Removed by Filter: 85
23. 0.021 0.021 ↑ 1.0 7 1

Seq Scan on "Statuses" s (cost=0.00..1.26 rows=7 width=20) (actual time=0.015..0.021 rows=7 loops=1)

  • Filter: ("TenantId" = 2)
  • Rows Removed by Filter: 14
24. 0.007 0.007 ↑ 1.0 1 1

Index Scan using "ProductSpecification_IX_ProductId" on "ProductSpecification" p0 (cost=0.14..0.33 rows=1 width=13) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ("ProductId" = p."Id")
  • Filter: (strpos("Key", 'BusTypeName'::text) > 0)
25. 0.013 0.013 ↑ 1.0 1 1

Index Scan using "NodeSpecification_IX_NodeId" on "NodeSpecification" n1 (cost=0.14..0.83 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)

  • Index Cond: ("NodeId" = n."Id")
  • Filter: ((("Value")::text = '1'::text) AND (strpos(("Key")::text, 'IsActive'::text) > 0))