2. WHERE = (equality filter)

Return only the rows whose region column exactly equals the string 'IN'. Because we declared an index on region in the schema, the planner promotes this to an IndexScan - the matching row is found by direct lookup rather than by scanning every row.

• "Find the row(s) where this column equals this value" - the most common filter shape.
• Lookups by user ID, status, region, category - any single equality predicate.
• The column you're filtering on should be either the primary key or covered by a secondary index. Without an index, equality still works but becomes a full scan.

Same schema and seed data as Example 1 - if you haven't loaded the customers yet, set those up first.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, email, region FROM shop.customers WHERE region = '\''IN'\'' LIMIT 10"
  }'

result = db.sql("""
    SELECT id, email, region
      FROM shop.customers
     WHERE region = 'IN'
     LIMIT 10
""")
for row in result.rows:
    print(row["id"], row["email"], row["region"])

const result = await db.sql(`
  SELECT id, email, region
    FROM shop.customers
   WHERE region = 'IN'
   LIMIT 10
`);
if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row.id, row.email, row.region);
  }
}

result, _ := db.SQL(ctx, `
    SELECT id, email, region
      FROM shop.customers
     WHERE region = 'IN'
     LIMIT 10
`)
for _, row := range result.Rows {
    fmt.Println(row["id"], row["email"], row["region"])
}

{
  "kind": "select",
  "rows": [
    { "id": "c_1", "email": "alice@example.com", "region": "IN" }
  ]
}

• The translator builds a Filter(region = 'IN') → Scan(shop.customers) plan.
• The optimiser checks the schema for indexes covering the filtered column. by_region covers region, so the plan is rewritten to IndexScan(by_region, region = 'IN').
• The IndexScan goes directly to the matching keys instead of iterating the whole row store. Lookup is sub-linear instead of linear in row count.

Run EXPLAIN SELECT ... on the same query to see whether your indexes are being used.