From e3f005d974ce32ca9f74328067efafcb1217c87f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 12 Nov 2018 11:19:04 -0500 Subject: [PATCH] Limit the number of index clauses considered in choose_bitmap_and(). classify_index_clause_usage() is O(N^2) in the number of distinct index qual clauses it considers, because of its use of a simple search list to store them. For nearly all queries, that's fine because only a few clauses will be considered. But Alexander Kuzmenkov reported a machine-generated query with 80000 (!) index qual clauses, which caused this code to take forever. Somewhat remarkably, this is the only O(N^2) behavior we now have for such a query, so let's fix it. We can get rid of the O(N^2) runtime for cases like this without much damage to the functionality of choose_bitmap_and() by separating out paths with "too many" qual or pred clauses, and deeming them to always be nonredundant with other paths. Then their clauses needn't go into the search list, so it doesn't get too long, but we don't lose the ability to consider bitmap AND plans altogether. I set the threshold for "too many" to be 100 clauses per path, which should be plenty to ensure no change in planning behavior for normal queries. There are other things we could do to make this go faster, but it's not clear that it's worth any additional effort. 80000 qual clauses require a whole lot of work in many other places, too. The code's been like this for a long time, so back-patch to all supported branches. The troublesome query only works back to 9.5 (in 9.4 it fails with stack overflow in the parser); so I'm not sure that fixing this in 9.4 has any real-world benefit, but perhaps it does. Discussion: https://postgr.es/m/90c5bdfa-d633-dabe-9889-3cf3e1acd443@postgrespro.ru --- src/backend/optimizer/path/indxpath.c | 32 ++++++++++++++++++++++++++- 1 file changed, 31 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index f295558f76..5f46415394 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -67,6 +67,7 @@ typedef struct List *quals; /* the WHERE clauses it uses */ List *preds; /* predicates of its partial index(es) */ Bitmapset *clauseids; /* quals+preds represented as a bitmapset */ + bool unclassifiable; /* has too many quals+preds to process? */ } PathClauseUsage; /* Callback argument for ec_member_matches_indexcol */ @@ -1447,9 +1448,18 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths) Path *ipath = (Path *) lfirst(l); pathinfo = classify_index_clause_usage(ipath, &clauselist); + + /* If it's unclassifiable, treat it as distinct from all others */ + if (pathinfo->unclassifiable) + { + pathinfoarray[npaths++] = pathinfo; + continue; + } + for (i = 0; i < npaths; i++) { - if (bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids)) + if (!pathinfoarray[i]->unclassifiable && + bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids)) break; } if (i < npaths) @@ -1484,6 +1494,10 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths) * For each surviving index, consider it as an "AND group leader", and see * whether adding on any of the later indexes results in an AND path with * cheaper total cost than before. Then take the cheapest AND group. + * + * Note: paths that are either clauseless or unclassifiable will have + * empty clauseids, so that they will not be rejected by the clauseids + * filter here, nor will they cause later paths to be rejected by it. */ for (i = 0; i < npaths; i++) { @@ -1711,6 +1725,21 @@ classify_index_clause_usage(Path *path, List **clauselist) result->preds = NIL; find_indexpath_quals(path, &result->quals, &result->preds); + /* + * Some machine-generated queries have outlandish numbers of qual clauses. + * To avoid getting into O(N^2) behavior even in this preliminary + * classification step, we want to limit the number of entries we can + * accumulate in *clauselist. Treat any path with more than 100 quals + + * preds as unclassifiable, which will cause calling code to consider it + * distinct from all other paths. + */ + if (list_length(result->quals) + list_length(result->preds) > 100) + { + result->clauseids = NULL; + result->unclassifiable = true; + return result; + } + /* Build up a bitmapset representing the quals and preds */ clauseids = NULL; foreach(lc, result->quals) @@ -1728,6 +1757,7 @@ classify_index_clause_usage(Path *path, List **clauselist) find_list_position(node, clauselist)); } result->clauseids = clauseids; + result->unclassifiable = false; return result; } -- 2.40.0