prestodb/presto · error · PrestoException

QUERY_HAS_TOO_MANY_STAGES

QUERY_HAS_TOO_MANY_STAGES

Error message

Number of stages in the query (%s) exceeds the allowed maximum (%s). If the query contains multiple DISTINCTs, please set the 'use_mark_distinct' session property to false. If the query contains multiple CTEs that are referenced more than once, please create temporary table(s) for one or more of the CTEs.

What it means

Presto fragments a plan into stages and enforces a soft limit on the total number of stages per query (max StageCount via query.max-stage-count / session property). If fragmenting the plan produces more stages than allowed, sanityCheckFragmentedPlan throws QUERY_HAS_TOO_MANY_STAGES with guidance about DISTINCTs and repeated CTEs, both of which multiply stages.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/planner/PlanFragmenterUtils.java:144

                getExchangeMaterializationStrategy(session),
                getQueryMaxStageCount(session),
                config.getStageCountWarningThreshold());

        return subPlan;
    }

    private static void sanityCheckFragmentedPlan(
            SubPlan subPlan,
            WarningCollector warningCollector,
            QueryManagerConfig.ExchangeMaterializationStrategy exchangeMaterializationStrategy,
            int maxStageCount,
            int stageCountSoftLimit)
    {
        subPlan.sanityCheck();

        int fragmentCount = subPlan.getAllFragments().size();
        if (fragmentCount > maxStageCount) {
            throw new PrestoException(QUERY_HAS_TOO_MANY_STAGES, format(
                    "Number of stages in the query (%s) exceeds the allowed maximum (%s). " + TOO_MANY_STAGES_MESSAGE,
                    fragmentCount, maxStageCount));
        }

        // When exchange materialization is enabled, only a limited number of stages will be executed concurrently
        //  (controlled by session property max_concurrent_materializations)
        if (exchangeMaterializationStrategy != QueryManagerConfig.ExchangeMaterializationStrategy.ALL) {
            if (fragmentCount > stageCountSoftLimit) {
                warningCollector.add(new PrestoWarning(TOO_MANY_STAGES, format(
                        "Number of stages in the query (%s) exceeds the soft limit (%s). " + TOO_MANY_STAGES_MESSAGE,
                        fragmentCount, stageCountSoftLimit)));
            }
        }
    }

    /*
     * In theory, recoverable grouped execution should be decided at query section level (i.e. a connected component of stages connected by remote exchanges).
     * This is because supporting mixed recoverable execution and non-recoverable execution within a query section adds unnecessary complications but provides little benefit,

View on GitHub (pinned to 55bb57d202)

Solutions

1. Set use_mark_distinct=false session property if the query contains multiple DISTINCTs (allows different plan shape per message)
2. Materialize heavily-referenced CTEs into temporary tables (CREATE TABLE AS SELECT, then query the temp table)
3. Increase the limit via the query.max-stage-count config property or corresponding session property if the cluster allows
4. Simplify/rewrite the query: fewer joins/subqueries, pre-aggregate into staging tables

Example fix

// before
WITH cte AS (SELECT ... ) SELECT ... FROM cte a JOIN cte b ...; -- cte referenced twice, many stages
// after
CREATE TABLE tmp_cte AS SELECT ...;
SELECT ... FROM tmp_cte a JOIN tmp_cte b ...;

// Rough client-side check before submitting very large queries
long tableScans = countTableScans(sql); // parse & count referenced tables
long estimatedStages = tableScans + countJoins(sql) + countDistincts(sql);
long maxStageCount = getMaxStageCountFromSession();
if (estimatedStages > maxStageCount) {
    throw new IllegalStateException("Query likely exceeds max stages (" + maxStageCount + "): simplify or materialize CTEs");
}

try {
    session.execute(sql);
} catch (PrestoException e) {
    if (e.getErrorCode().getName().equals("QUERY_HAS_TOO_MANY_STAGES")) {
        // retry with mark distinct disabled or after materializing CTEs
        session.setProperty("use_mark_distinct", "false");
        session.execute(sql);
    } else {
        throw e;
    }
}

When it happens

Trigger: A query plan fragments into more fragments than maxStageCount: many table scans/joins each creating stages, multiple DISTINCT aggregations (each adds a stage unless mark-distinct is used), or CTEs referenced multiple times being re-planned per reference.

Common situations: Very wide ad-hoc queries joining dozens of tables; analytic SQL with several DISTINCT clauses; CTEs (WITH clauses) referenced many times without materialization; clusters configured with a low query.max-stage-count.

Related errors

• EXCEEDED_PLAN_NODE_LIMIT
• NOT_SUPPORTED
• NODE_SELECTION_NOT_SUPPORTED
• INVALID_LIMIT_CLAUSE
• QUERY_PLANNING_TIMEOUT

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/ee92bc671668d1b4. Report an issue: GitHub.