{"record":{"id":"ee92bc671668d1b4","repo":"prestodb/presto","slug":"query-has-too-many-stages","errorCode":"QUERY_HAS_TOO_MANY_STAGES","errorMessage":"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.","messagePattern":"Number of stages in the query \\((.+?)\\) exceeds the allowed maximum \\((.+?)\\)\\. 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\\.","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/planner/PlanFragmenterUtils.java","lineNumber":144,"sourceCode":"                getExchangeMaterializationStrategy(session),\n                getQueryMaxStageCount(session),\n                config.getStageCountWarningThreshold());\n\n        return subPlan;\n    }\n\n    private static void sanityCheckFragmentedPlan(\n            SubPlan subPlan,\n            WarningCollector warningCollector,\n            QueryManagerConfig.ExchangeMaterializationStrategy exchangeMaterializationStrategy,\n            int maxStageCount,\n            int stageCountSoftLimit)\n    {\n        subPlan.sanityCheck();\n\n        int fragmentCount = subPlan.getAllFragments().size();\n        if (fragmentCount > maxStageCount) {\n            throw new PrestoException(QUERY_HAS_TOO_MANY_STAGES, format(\n                    \"Number of stages in the query (%s) exceeds the allowed maximum (%s). \" + TOO_MANY_STAGES_MESSAGE,\n                    fragmentCount, maxStageCount));\n        }\n\n        // When exchange materialization is enabled, only a limited number of stages will be executed concurrently\n        //  (controlled by session property max_concurrent_materializations)\n        if (exchangeMaterializationStrategy != QueryManagerConfig.ExchangeMaterializationStrategy.ALL) {\n            if (fragmentCount > stageCountSoftLimit) {\n                warningCollector.add(new PrestoWarning(TOO_MANY_STAGES, format(\n                        \"Number of stages in the query (%s) exceeds the soft limit (%s). \" + TOO_MANY_STAGES_MESSAGE,\n                        fragmentCount, stageCountSoftLimit)));\n            }\n        }\n    }\n\n    /*\n     * In theory, recoverable grouped execution should be decided at query section level (i.e. a connected component of stages connected by remote exchanges).\n     * This is because supporting mixed recoverable execution and non-recoverable execution within a query section adds unnecessary complications but provides little benefit,","sourceCodeStart":126,"sourceCodeEnd":162,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/planner/PlanFragmenterUtils.java#L126-L162","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Set use_mark_distinct=false session property if the query contains multiple DISTINCTs (allows different plan shape per message)","Materialize heavily-referenced CTEs into temporary tables (CREATE TABLE AS SELECT, then query the temp table)","Increase the limit via the query.max-stage-count config property or corresponding session property if the cluster allows","Simplify/rewrite the query: fewer joins/subqueries, pre-aggregate into staging tables"],"exampleFix":"// before\nWITH cte AS (SELECT ... ) SELECT ... FROM cte a JOIN cte b ...; -- cte referenced twice, many stages\n// after\nCREATE TABLE tmp_cte AS SELECT ...;\nSELECT ... FROM tmp_cte a JOIN tmp_cte b ...;","handlingStrategy":"validation","validationCode":"// Rough client-side check before submitting very large queries\nlong tableScans = countTableScans(sql); // parse & count referenced tables\nlong estimatedStages = tableScans + countJoins(sql) + countDistincts(sql);\nlong maxStageCount = getMaxStageCountFromSession();\nif (estimatedStages > maxStageCount) {\n    throw new IllegalStateException(\"Query likely exceeds max stages (\" + maxStageCount + \"): simplify or materialize CTEs\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    session.execute(sql);\n} catch (PrestoException e) {\n    if (e.getErrorCode().getName().equals(\"QUERY_HAS_TOO_MANY_STAGES\")) {\n        // retry with mark distinct disabled or after materializing CTEs\n        session.setProperty(\"use_mark_distinct\", \"false\");\n        session.execute(sql);\n    } else {\n        throw e;\n    }\n}","preventionTips":["Avoid many repeated CTE references — materialize into temp tables","Keep the number of DISTINCTs in a single query low","Know your cluster's query.max-stage-count and design queries within it","Break very large join graphs into staged ETL steps"],"tags":["presto","planner","stage-limit","query-complexity"],"backgroundTag":"query-too-many-stages","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}