From 3e4b3465b6345b75659e8f897976d4c810408762 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 26 Oct 2011 17:52:02 -0400 Subject: Improve planner's ability to recognize cases where an IN's RHS is unique. If the right-hand side of a semijoin is unique, then we can treat it like a normal join (or another way to say that is: we don't need to explicitly unique-ify the data before doing it as a normal join). We were recognizing such cases when the RHS was a sub-query with appropriate DISTINCT or GROUP BY decoration, but there's another way: if the RHS is a plain relation with unique indexes, we can check if any of the indexes prove the output is unique. Most of the infrastructure for that was there already in the join removal code, though I had to rearrange it a bit. Per reflection about a recent example in pgsql-performance. --- src/backend/optimizer/util/pathnode.c | 27 +++++++++++++++++++++++++-- 1 file changed, 25 insertions(+), 2 deletions(-) (limited to 'src/backend/optimizer/util/pathnode.c') diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 6aa34412def..1e7aac95ef4 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1021,8 +1021,8 @@ create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, pathnode->path.parent = rel; /* - * Treat the output as always unsorted, since we don't necessarily have - * pathkeys to represent it. + * Assume the output is unsorted, since we don't necessarily have pathkeys + * to represent it. (This might get overridden below.) */ pathnode->path.pathkeys = NIL; @@ -1030,6 +1030,29 @@ create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, pathnode->in_operators = in_operators; pathnode->uniq_exprs = uniq_exprs; + /* + * If the input is a relation and it has a unique index that proves the + * uniq_exprs are unique, then we don't need to do anything. Note that + * relation_has_unique_index_for automatically considers restriction + * clauses for the rel, as well. + */ + if (rel->rtekind == RTE_RELATION && all_btree && + relation_has_unique_index_for(root, rel, NIL, + uniq_exprs, in_operators)) + { + pathnode->umethod = UNIQUE_PATH_NOOP; + pathnode->rows = rel->rows; + pathnode->path.startup_cost = subpath->startup_cost; + pathnode->path.total_cost = subpath->total_cost; + pathnode->path.pathkeys = subpath->pathkeys; + + rel->cheapest_unique_path = (Path *) pathnode; + + MemoryContextSwitchTo(oldcontext); + + return pathnode; + } + /* * If the input is a subquery whose output must be unique already, then we * don't need to do anything. The test for uniqueness has to consider -- cgit v1.2.3