explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 49.468 534.614 ↑ 2.3 17,087 1

Unique (cost=10,114.53..11,214.84 rows=40,011 width=128) (actual time=446.496..534.614 rows=17,087 loops=1)

2. 243.246 485.146 ↓ 1.0 40,083 1

Sort (cost=10,114.53..10,214.56 rows=40,011 width=128) (actual time=446.493..485.146 rows=40,083 loops=1)

  • Sort Key: s."StatusName", s."StatusId", a1."UpdatedAt", (COALESCE(h."Id", '0'::bigint)), (COALESCE(h."AssetStatusId", '0'::bigint)), (COALESCE(h."DiagnosisCode", ''::text)), (COALESCE(h."Protocol", ''::text)), (COALESCE(h."HealthConditionCauseCommonId", '0'::bigint)), (COALESCE(h."HealthConditionRemedyCommonId", '0'::bigint)), (CASE WHEN (COALESCE(h."Protocol", ''::text) <> ''::text) THEN 1 WHEN (COALESCE(h."DiagnosisCode", ''::text) <> ''::text) THEN 2 ELSE 3 END)
  • Sort Method: external merge Disk: 3,600kB
3. 72.817 241.900 ↓ 1.0 40,083 1

Hash Left Join (cost=2,156.87..4,455.57 rows=40,011 width=128) (actual time=58.176..241.900 rows=40,083 loops=1)

  • Hash Cond: (a1."Id" = h."AssetStatusId")
4. 35.473 140.764 ↓ 1.0 40,075 1

Hash Join (cost=1,432.96..3,155.87 rows=40,011 width=36) (actual time=29.706..140.764 rows=40,075 loops=1)

  • Hash Cond: ((a1."Name")::text = (s."StatusName")::text)
5. 47.315 105.076 ↓ 1.0 40,075 1

Hash Join (cost=1,428.25..2,450.97 rows=40,011 width=27) (actual time=29.456..105.076 rows=40,075 loops=1)

  • Hash Cond: (a0."AssetStatusId" = a1."Id")
6. 29.039 29.039 ↓ 1.0 40,075 1

Seq Scan on "AssetAssetStatus" a0 (cost=0.00..917.68 rows=40,011 width=16) (actual time=0.395..29.039 rows=40,075 loops=1)

  • Filter: ("AssetId" = 71)
  • Rows Removed by Filter: 1,899
7. 15.180 28.722 ↑ 1.0 42,100 1

Hash (cost=902.00..902.00 rows=42,100 width=19) (actual time=28.721..28.722 rows=42,100 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,619kB
8. 13.542 13.542 ↑ 1.0 42,100 1

Seq Scan on "AssetStatus" a1 (cost=0.00..902.00 rows=42,100 width=19) (actual time=0.034..13.542 rows=42,100 loops=1)

9. 0.025 0.215 ↑ 1.0 7 1

Hash (cost=4.62..4.62 rows=7 width=28) (actual time=0.215..0.215 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.006 0.190 ↑ 1.0 7 1

Nested Loop (cost=0.00..4.62 rows=7 width=28) (actual time=0.180..0.190 rows=7 loops=1)

11. 0.152 0.152 ↑ 1.0 1 1

Seq Scan on "Asset" a (cost=0.00..3.29 rows=1 width=8) (actual time=0.151..0.152 rows=1 loops=1)

  • Filter: (("Id" = 71) AND ("TenantId" = 2))
  • Rows Removed by Filter: 85
12. 0.032 0.032 ↑ 1.0 7 1

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

  • Filter: ("TenantId" = 2)
  • Rows Removed by Filter: 14
13. 11.773 28.319 ↑ 1.0 18,485 1

Hash (cost=492.85..492.85 rows=18,485 width=65) (actual time=28.319..28.319 rows=18,485 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,143kB
14. 16.546 16.546 ↑ 1.0 18,485 1

Seq Scan on "HealthCondition" h (cost=0.00..492.85 rows=18,485 width=65) (actual time=0.033..16.546 rows=18,485 loops=1)