← All workshopsRead Why Does a Short Query Repeat So Much Work?

SQL

Compare measured work only for matching answers

The cheap grouped plan lost an asset

The report has two candidate execution measurements. Compare their work only after establishing that they return the same required answer.

BOTIE

The grouped plan visits fewer rows but omits P8. Which plan can be considered under the required-answer contract?
The inputs we are comparing

Measured plan records

Grouping avoids repeated scans

[
  {
    "id": "repeat",
    "answers": [
      "P7",
      "P8"
    ],
    "scans": [
      {
        "rows_per_loop": 100,
        "loops": 20
      }
    ]
  },
  {
    "id": "group",
    "answers": [
      "P7",
      "P8"
    ],
    "scans": [
      {
        "rows_per_loop": 100,
        "loops": 1
      }
    ]
  }
]

The cheap plan lost an asset

[
  {
    "id": "repeat",
    "answers": [
      "P7",
      "P8"
    ],
    "scans": [
      {
        "rows_per_loop": 100,
        "loops": 20
      }
    ]
  },
  {
    "id": "group",
    "answers": [
      "P7"
    ],
    "scans": [
      {
        "rows_per_loop": 10,
        "loops": 1
      }
    ]
  }
]

Required answer identities

Same in both cases

  1. 0P7
  2. 1P8
Result for grouping avoids repeated scans
group
Choose an explanation to test
A selective lookup examines fewer rows than a full aggregate

This check uses the pieces from “Compare comparable plan measurements”.

BOTIE

Both plans return P2. The lookup scans two rows once; the aggregate scans two hundred rows once. Predict the smaller measured row-visit plan without assuming grouping always wins.
Measured plan records
[
  {
    "id": "lookup",
    "answers": [
      "P2"
    ],
    "scans": [
      {
        "rows_per_loop": 2,
        "loops": 1
      }
    ]
  },
  {
    "id": "aggregate",
    "answers": [
      "P2"
    ],
    "scans": [
      {
        "rows_per_loop": 200,
        "loops": 1
      }
    ]
  }
]
Required answer identities
  1. 0P2

Use a number, text, or an array matching the requested result. For a matrix, put each row in brackets. Saving your prediction runs your current construction on these inputs and checks the earlier examples too.

Build a construction in the workspace above first. This case will test those pieces.

Which costs besides row visits would you measure before choosing a production query?

Read estimates and observations separately, preserve semantics, and measure a representative workload.

Invented row-visit counts omit I/O, cache, parallelism and memory effects. They do not predict real execution time.

Work through the SQL challenges →
Continue the courseLet a Search Value Stay a Value →
← Find One Pump’s Readings Without Checking Every Pump