explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GujS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 21.302 334.626 ↓ 267.0 267 1

Nested Loop (cost=3,030.55..21,235.46 rows=1 width=161) (actual time=70.467..334.626 rows=267 loops=1)

  • Join Filter: ((((((aad."AddonData" -> 'Danger'::text) ->> 'RepairFactor'::text))::integer >= 700) AND ((((aad."AddonData" -> 'Danger'::text) ->> 'RepairFactor'::text))::integer <= 799)) OR ((art."ClassId" = '30b6e3fd-7664-45de-805f-53859d897281'::uuid) AND ((aam."MeasuredDepth" / aam."FeatureWallThickness") > '0.5'::double precision) AND (((aad."AddonData" -> 'Danger'::text) ->> 'CurrentState'::text) IS NULL)))
  • Rows Removed by Join Filter: 11525
2. 10.805 218.988 ↓ 11,792.0 11,792 1

Nested Loop (cost=3,030.12..21,234.87 rows=1 width=448) (actual time=63.289..218.988 rows=11,792 loops=1)

3. 5.840 102.055 ↓ 310.3 11,792 1

Hash Join (cost=3,029.68..21,210.12 rows=38 width=184) (actual time=63.235..102.055 rows=11,792 loops=1)

  • Hash Cond: (deftype."Id" = deftdefgr."DefectTypeId")
4. 5.337 96.063 ↓ 287.6 11,792 1

Hash Join (cost=3,023.91..21,203.81 rows=41 width=212) (actual time=63.070..96.063 rows=11,792 loops=1)

  • Hash Cond: (art."DiagnosticObjectId" = dcoord."DiagnosticObjectId")
5. 3.086 30.484 ↓ 1.3 11,792 1

Nested Loop (cost=1,056.09..19,191.43 rows=9,025 width=183) (actual time=2.800..30.484 rows=11,792 loops=1)

6. 0.003 0.117 ↑ 1.0 1 1

Nested Loop (cost=4.97..10.61 rows=1 width=24) (actual time=0.098..0.117 rows=1 loops=1)

7. 0.027 0.093 ↑ 1.0 1 1

Hash Join (cost=4.83..6.44 rows=1 width=24) (actual time=0.077..0.093 rows=1 loops=1)

  • Hash Cond: (cd."Id" = idt."DiameterId")
8. 0.011 0.011 ↑ 1.0 48 1

Seq Scan on "ConstructiveDiameter" cd (cost=0.00..1.48 rows=48 width=12) (actual time=0.003..0.011 rows=48 loops=1)

9. 0.003 0.055 ↑ 1.0 1 1

Hash (cost=4.81..4.81 rows=1 width=20) (actual time=0.054..0.055 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.052 0.052 ↑ 1.0 1 1

Seq Scan on "InnerDiagnosticTarget" idt (cost=0.00..4.81 rows=1 width=20) (actual time=0.051..0.052 rows=1 loops=1)

  • Filter: ("Id" = 'cfaaa6c0-b548-4b9b-9caf-5e6a8936bc8c'::uuid)
  • Rows Removed by Filter: 144
11. 0.021 0.021 ↑ 1.0 1 1

Index Only Scan using "PK_DiagnocticTarget" on "DiagnosticTarget" dt (cost=0.14..4.16 rows=1 width=16) (actual time=0.018..0.021 rows=1 loops=1)

  • Index Cond: ("Id" = 'cfaaa6c0-b548-4b9b-9caf-5e6a8936bc8c'::uuid)
  • Heap Fetches: 0
12. 0.000 27.281 ↓ 1.3 11,792 1

Gather (cost=1,051.12..19,090.57 rows=9,025 width=191) (actual time=2.698..27.281 rows=11,792 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
13. 2.973 28.270 ↓ 1.1 5,896 2

Hash Join (cost=51.12..17,188.07 rows=5,309 width=191) (actual time=1.222..28.270 rows=5,896 loops=2)

  • Hash Cond: (ac."DefectTypeId" = deftype."Id")
14. 3.065 25.174 ↓ 1.1 5,948 2

Hash Join (cost=45.83..17,168.57 rows=5,309 width=175) (actual time=0.697..25.174 rows=5,948 loops=2)

  • Hash Cond: (art."ClassId" = ac."Id")
15. 15.630 21.857 ↓ 1.1 5,948 2

Nested Loop (cost=36.06..17,144.59 rows=5,309 width=119) (actual time=0.416..21.857 rows=5,948 loops=2)

  • Join Filter: (ddo."Id" = art."DiagnosticObjectId")
16. 5.743 6.182 ↑ 3.0 268 2

Nested Loop (cost=35.63..10,653.63 rows=809 width=64) (actual time=0.354..6.182 rows=268 loops=2)

17. 0.000 0.419 ↑ 3.1 268 2

Parallel Bitmap Heap Scan on "DiagnosticObject" ddo (cost=35.20..4,190.52 rows=818 width=32) (actual time=0.261..0.419 rows=268 loops=2)

  • Recheck Cond: ("DiagnosticTargetId" = 'cfaaa6c0-b548-4b9b-9caf-5e6a8936bc8c'::uuid)
  • Heap Blocks: exact=2
18. 0.428 0.428 ↑ 2.6 535 1

Bitmap Index Scan on "IX_DiagnosticSection_DiagnosticTargetId" (cost=0.00..34.85 rows=1,390 width=0) (actual time=0.428..0.428 rows=535 loops=1)

  • Index Cond: ("DiagnosticTargetId" = 'cfaaa6c0-b548-4b9b-9caf-5e6a8936bc8c'::uuid)
19. 0.020 0.020 ↑ 1.0 1 535

Index Scan using "PK_Section" on "Section" ds (cost=0.43..7.90 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=535)

  • Index Cond: ("Id" = ddo."Id")
20. 0.045 0.045 ↑ 10.0 22 535

Index Scan using "IX_DiagnosticArtifact_DiagnosticObjectId" on "Artifact" art (cost=0.43..5.26 rows=221 width=55) (actual time=0.030..0.045 rows=22 loops=535)

  • Index Cond: ("DiagnosticObjectId" = ds."Id")
21. 0.109 0.252 ↑ 1.0 212 2

Hash (cost=7.12..7.12 rows=212 width=72) (actual time=0.251..0.252 rows=212 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
22. 0.143 0.143 ↑ 1.0 212 2

Seq Scan on "ArtifactClass" ac (cost=0.00..7.12 rows=212 width=72) (actual time=0.019..0.143 rows=212 loops=2)

23. 0.050 0.123 ↑ 1.0 146 2

Hash (cost=3.46..3.46 rows=146 width=16) (actual time=0.123..0.123 rows=146 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.073 0.073 ↑ 1.0 146 2

Seq Scan on "DefectType" deftype (cost=0.00..3.46 rows=146 width=16) (actual time=0.026..0.073 rows=146 loops=2)

25. 19.084 60.242 ↓ 8.6 57,699 1

Hash (cost=1,883.97..1,883.97 rows=6,708 width=93) (actual time=60.242..60.242 rows=57,699 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 7553kB
26. 28.745 41.158 ↓ 8.6 57,699 1

Hash Join (cost=1.12..1,883.97 rows=6,708 width=93) (actual time=0.032..41.158 rows=57,699 loops=1)

  • Hash Cond: (dcoord."CoordinateSystemId" = cs."Id")
27. 12.397 12.397 ↑ 1.0 60,374 1

Seq Scan on "DiagnosticObjectCoordinate" dcoord (cost=0.00..1,649.74 rows=60,374 width=109) (actual time=0.007..12.397 rows=60,374 loops=1)

28. 0.003 0.016 ↑ 1.0 1 1

Hash (cost=1.11..1.11 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on "CoordinateSystem" cs (cost=0.00..1.11 rows=1 width=16) (actual time=0.009..0.013 rows=1 loops=1)

  • Filter: ("Name" = 'WGS84'::text)
  • Rows Removed by Filter: 8
30. 0.038 0.152 ↑ 1.0 135 1

Hash (cost=4.09..4.09 rows=135 width=20) (actual time=0.152..0.152 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
31. 0.071 0.114 ↑ 1.0 135 1

Hash Join (cost=1.23..4.09 rows=135 width=20) (actual time=0.038..0.114 rows=135 loops=1)

  • Hash Cond: (deftdefgr."DefectGroupId" = defgr."Id")
32. 0.029 0.029 ↑ 1.0 135 1

Seq Scan on "DefectTypeDefectGroupRef" deftdefgr (cost=0.00..2.35 rows=135 width=20) (actual time=0.010..0.029 rows=135 loops=1)

33. 0.006 0.014 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.014..0.014 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.008 0.008 ↑ 1.0 10 1

Seq Scan on "DefectGroup" defgr (cost=0.00..1.10 rows=10 width=4) (actual time=0.003..0.008 rows=10 loops=1)

35. 106.128 106.128 ↑ 1.0 1 11,792

Index Scan using "PK_ArtifactAddonData" on "ArtifactAddonData" aad (cost=0.43..0.65 rows=1 width=264) (actual time=0.009..0.009 rows=1 loops=11,792)

  • Index Cond: ("Id" = art."Id")
  • Filter: (((((("AddonData" -> 'Danger'::text) ->> 'RepairFactor'::text))::integer >= 700) AND (((("AddonData" -> 'Danger'::text) ->> 'RepairFactor'::text))::integer <= 799)) OR ((("AddonData" -> 'Danger'::text) ->> 'CurrentState'::text) IS NULL))
36. 94.336 94.336 ↑ 1.0 1 11,792

Index Scan using "PK_ArtifactAddonMeasured" on "ArtifactAddonMeasured" aam (cost=0.43..0.52 rows=1 width=38) (actual time=0.008..0.008 rows=1 loops=11,792)

  • Index Cond: ("Id" = art."Id")