Querying Pinot

Pinot queries run through the broker and are written in SQL. This page is the wayfinding layer for people who want to query data, understand which engine to use, and know where to look when a query needs tuning.

How to start

1. Write the query in Pinot SQL.

2. Decide whether the single-stage engine is enough or whether you need multi-stage features such as joins and subqueries.

3. Use query options to control runtime behavior.

4. Inspect the plan or result shape when you need to debug performance.

SET useMultistageEngine = true;
SELECT city, COUNT(*)
FROM stores
GROUP BY city
LIMIT 10;

What matters most

Pinot SQL uses the Apache Calcite parser with the MYSQL_ANSI dialect. In practice, that means you should pay attention to identifier quoting, literal quoting, and engine-specific capabilities.

If you are debugging a slow or surprising query, the most useful follow-up pages are:

• SQL syntax

• Query options

• Grouping algorithm

• Query quotas

• Query cancellation

• Cursor pagination

• Correlation IDs

• Explain plan

• Multi-stage explain plan

• SSE vs MSE

Group-by quirks (default LIMIT, trimming, ORDER BY)

These behaviors catch many users by surprise. Details and tuning knobs live in Grouping algorithm and Query options.

Default LIMIT is 10

On the single-stage engine (SSE), if a query omits LIMIT, the broker applies a default of 10 rows (pinot.broker.default.query.limit). This applies to selection queries and GROUP BY queries, so an SSE group-by without an explicit LIMIT returns at most 10 groups.

The multi-stage engine (MSE) does not apply this broker default. Still set an explicit LIMIT whenever you care about result size or want intentional truncation.

Tail trimming on GROUP BY

For SSE group-by with ORDER BY, Pinot may trim tail groups while aggregating so servers stay within memory limits. Where trimming is enabled, the candidate size is based on max(minTrimSize, 5 * LIMIT); segment trimming is disabled by default, while server and broker reduction have their own defaults. Pinot ranks candidates using the query's ORDER BY. See Grouping algorithm for the stage-specific settings.

Implications:

• Results can be approximate when cardinality is high relative to LIMIT and trim thresholds.

• numGroupsLimitReached=true means a group operator reached its hard group cap; increasing a trim size cannot recover groups already dropped at that cap.

• Raise the relevant trim sizes when you need a larger candidate set, or raise LIMIT when you also need more rows returned. Both choices increase memory use.

GROUP BY with ORDER BY vs without ORDER BY

Do not assume a stable or ranked group set without ORDER BY. For a deterministic subset, set accurateGroupByWithoutOrderBy=true; SSE then keeps the lexicographically smallest group keys during server and broker reduction. This does not rank by an aggregate and cannot recover keys dropped by numGroupsLimit. See Query options.

HAVING and post-aggregation

On SSE, HAVING filters the merged group candidates after aggregation and any earlier group trimming. If HAVING prefers groups that trimming already dropped (for example HAVING SUM(x) < 100 with ORDER BY SUM(x) DESC), matching groups may be missing—increase trim sizes or adjust ordering.

Post-aggregation expressions combine aggregated values (and group keys) in SELECT, HAVING, or ORDER BY after the aggregates are computed:

Query options (names and syntax)

Use SET statements or OPTION (...) to pass per-query options. Recognized keys resolve case-insensitively to the canonical camelCase names listed in Query options (for example timeoutMs, numGroupsLimit, minSegmentGroupTrimSize, useMultistageEngine). Prefer canonical spelling: unknown names can be accepted but ignored.

When to use which engine

Single-stage execution is the default path for straightforward filtering, aggregation, and top-K style queries.

Use multi-stage execution when you need features that are not available in single-stage mode, such as:

• joins

• subqueries

• window functions

• more complex distributed query shapes

As a rule of thumb: use SSE for simple filtering, aggregation, and top-K queries; use MSE when your query shape requires joins, subqueries, window functions, or other advanced relational operators. For a detailed comparison, see SSE vs MSE.

Next step

Read SQL syntax for the query language itself, then move to Query options or Explain plan when you need control or diagnostics.

Related pages

• Querying & SQL controls

• SQL syntax

• Query options

• Grouping algorithm

• Explain plan

• Multi-stage explain plan

• SSE vs MSE

• SQL syntax and operators reference

Identifier vs Literal

In Pinot SQL:

• Double quotes(") are used to force string identifiers, e.g. column names

• Single quotes(') are used to enclose string literals. If the string literal also contains a single quote, escape this with a single quote e.g '''Pinot''' to match the string literal 'Pinot'

Misusing those might cause unexpected query results, like the following examples:

• WHERE a='b' means the predicate on the column a equals to a string literal value 'b'

• WHERE a="b" means the predicate on the column a equals to the value of the column b

If your column names use reserved keywords (e.g. timestamp or date) or special characters, you will need to use double quotes when referring to them in queries.

Note: Define decimal literals within quotes to preserve precision.

Example Queries

Selection

Aggregation

Grouping on Aggregation

Ordering on Aggregation

Filtering groups with HAVING

Post-aggregation expressions

Filtering

For performant filtering of IDs in a list, see Filtering with IdSet.

Filtering with NULL predicate

Selection (Projection)

Ordering on Selection

Pagination on Selection

Note that results might not be consistent if the ORDER BY column has the same value in multiple rows.

Wild-card match (in WHERE clause only)

The example below counts rows where the column airlineName starts with U:

Note: REGEXP_LIKE also supports case insensitive search using the i flag as the third parameter.

Case-When Statement

Pinot supports the CASE-WHEN-ELSE statement, as shown in the following two examples:

UDF

Pinot doesn't currently support injecting functions. Functions have to be implemented within Pinot, as shown below:

For more examples, see Transform Function in Aggregation Grouping.

BYTES column

Pinot supports queries on BYTES column using hex strings. The query response also uses hex strings to represent bytes values.

The query below fetches all the rows for a given UID: