1 /*-------------------------------------------------------------------------
4 * Routines to determine which indices are usable for scanning a
5 * given relation, and create IndexPaths accordingly.
7 * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
8 * Portions Copyright (c) 1994, Regents of the University of California
12 * $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.173 2005/04/11 23:06:55 tgl Exp $
14 *-------------------------------------------------------------------------
20 #include "access/nbtree.h"
21 #include "catalog/pg_amop.h"
22 #include "catalog/pg_namespace.h"
23 #include "catalog/pg_opclass.h"
24 #include "catalog/pg_operator.h"
25 #include "catalog/pg_proc.h"
26 #include "catalog/pg_type.h"
27 #include "executor/executor.h"
28 #include "nodes/makefuncs.h"
29 #include "optimizer/clauses.h"
30 #include "optimizer/cost.h"
31 #include "optimizer/pathnode.h"
32 #include "optimizer/paths.h"
33 #include "optimizer/restrictinfo.h"
34 #include "optimizer/var.h"
35 #include "parser/parse_expr.h"
36 #include "rewrite/rewriteManip.h"
37 #include "utils/builtins.h"
38 #include "utils/catcache.h"
39 #include "utils/lsyscache.h"
40 #include "utils/pg_locale.h"
41 #include "utils/selfuncs.h"
42 #include "utils/syscache.h"
46 * DoneMatchingIndexKeys() - MACRO
48 #define DoneMatchingIndexKeys(classes) (classes[0] == InvalidOid)
50 #define is_indexable_operator(clause,opclass,indexkey_on_left) \
51 (indexable_operator(clause,opclass,indexkey_on_left) != InvalidOid)
53 #define IsBooleanOpclass(opclass) \
54 ((opclass) == BOOL_BTREE_OPS_OID || (opclass) == BOOL_HASH_OPS_OID)
57 static List *group_clauses_by_indexkey_for_join(Query *root,
60 JoinType jointype, bool isouterjoin);
61 static bool match_clause_to_indexcol(IndexOptInfo *index,
62 int indexcol, Oid opclass,
64 static bool match_join_clause_to_indexcol(IndexOptInfo *index,
65 int indexcol, Oid opclass,
67 static Oid indexable_operator(Expr *clause, Oid opclass,
68 bool indexkey_on_left);
69 static bool pred_test_recurse(Node *clause, Node *predicate);
70 static bool pred_test_simple_clause(Expr *predicate, Node *clause);
71 static Relids indexable_outerrelids(IndexOptInfo *index);
72 static Path *make_innerjoin_index_path(Query *root, IndexOptInfo *index,
74 static bool match_boolean_index_clause(Node *clause, int indexcol,
76 static bool match_special_index_operator(Expr *clause, Oid opclass,
77 bool indexkey_on_left);
78 static Expr *expand_boolean_index_clause(Node *clause, int indexcol,
80 static List *expand_indexqual_condition(RestrictInfo *rinfo, Oid opclass);
81 static List *prefix_quals(Node *leftop, Oid opclass,
82 Const *prefix, Pattern_Prefix_Status pstatus);
83 static List *network_prefix_quals(Node *leftop, Oid expr_op, Oid opclass,
85 static Datum string_to_datum(const char *str, Oid datatype);
86 static Const *string_to_const(const char *str, Oid datatype);
90 * create_index_paths()
91 * Generate all interesting index paths for the given relation.
92 * Candidate paths are added to the rel's pathlist (using add_path).
94 * To be considered for an index scan, an index must match one or more
95 * restriction clauses or join clauses from the query's qual condition,
96 * or match the query's ORDER BY condition.
98 * There are two basic kinds of index scans. A "plain" index scan uses
99 * only restriction clauses (possibly none at all) in its indexqual,
100 * so it can be applied in any context. An "innerjoin" index scan uses
101 * join clauses (plus restriction clauses, if available) in its indexqual.
102 * Therefore it can only be used as the inner relation of a nestloop
103 * join against an outer rel that includes all the other rels mentioned
104 * in its join clauses. In that context, values for the other rels'
105 * attributes are available and fixed during any one scan of the indexpath.
107 * An IndexPath is generated and submitted to add_path() for each plain index
108 * scan this routine deems potentially interesting for the current query.
110 * We also determine the set of other relids that participate in join
111 * clauses that could be used with each index. The actually best innerjoin
112 * path will be generated for each outer relation later on, but knowing the
113 * set of potential otherrels allows us to identify equivalent outer relations
114 * and avoid repeated computation.
116 * 'rel' is the relation for which we want to generate index paths
118 * Note: check_partial_indexes() must have been run previously.
121 create_index_paths(Query *root, RelOptInfo *rel)
123 Relids all_join_outerrelids = NULL;
126 foreach(ilist, rel->indexlist)
128 IndexOptInfo *index = (IndexOptInfo *) lfirst(ilist);
129 List *restrictclauses;
130 List *index_pathkeys;
131 List *useful_pathkeys;
132 bool index_is_ordered;
133 Relids join_outerrelids;
135 /* Ignore partial indexes that do not match the query */
136 if (index->indpred != NIL && !index->predOK)
140 * 1. Match the index against non-OR restriction clauses. (OR
141 * clauses will be considered later by orindxpath.c.)
143 restrictclauses = group_clauses_by_indexkey(index);
146 * 2. Compute pathkeys describing index's ordering, if any, then
147 * see how many of them are actually useful for this query.
149 index_pathkeys = build_index_pathkeys(root, index,
150 ForwardScanDirection);
151 index_is_ordered = (index_pathkeys != NIL);
152 useful_pathkeys = truncate_useless_pathkeys(root, rel,
156 * 3. Generate an indexscan path if there are relevant restriction
157 * clauses OR the index ordering is potentially useful for later
158 * merging or final output ordering.
160 * If there is a predicate, consider it anyway since the index
161 * predicate has already been found to match the query. The
162 * selectivity of the predicate might alone make the index useful.
164 if (restrictclauses != NIL ||
165 useful_pathkeys != NIL ||
166 index->indpred != NIL)
167 add_path(rel, (Path *)
168 create_index_path(root, index,
172 ForwardScanDirection :
173 NoMovementScanDirection));
176 * 4. If the index is ordered, a backwards scan might be
177 * interesting. Currently this is only possible for a DESC query
180 if (index_is_ordered)
182 index_pathkeys = build_index_pathkeys(root, index,
183 BackwardScanDirection);
184 useful_pathkeys = truncate_useless_pathkeys(root, rel,
186 if (useful_pathkeys != NIL)
187 add_path(rel, (Path *)
188 create_index_path(root, index,
191 BackwardScanDirection));
195 * 5. Examine join clauses to see which ones are potentially
196 * usable with this index, and generate the set of all other
197 * relids that participate in such join clauses. We'll use this
198 * set later to recognize outer rels that are equivalent for
199 * joining purposes. We compute both per-index and
200 * overall-for-relation sets.
202 join_outerrelids = indexable_outerrelids(index);
203 index->outer_relids = join_outerrelids;
204 all_join_outerrelids = bms_add_members(all_join_outerrelids,
208 rel->index_outer_relids = all_join_outerrelids;
212 /****************************************************************************
213 * ---- ROUTINES TO CHECK RESTRICTIONS ----
214 ****************************************************************************/
218 * group_clauses_by_indexkey
219 * Find restriction clauses that can be used with an index.
221 * Returns a list of sublists of RestrictInfo nodes for clauses that can be
222 * used with this index. Each sublist contains clauses that can be used
223 * with one index key (in no particular order); the top list is ordered by
224 * index key. (This is depended on by expand_indexqual_conditions().)
226 * Note that in a multi-key index, we stop if we find a key that cannot be
227 * used with any clause. For example, given an index on (A,B,C), we might
228 * return ((C1 C2) (C3 C4)) if we find that clauses C1 and C2 use column A,
229 * clauses C3 and C4 use column B, and no clauses use column C. But if
230 * no clauses match B we will return ((C1 C2)), whether or not there are
231 * clauses matching column C, because the executor couldn't use them anyway.
232 * Therefore, there are no empty sublists in the result.
235 group_clauses_by_indexkey(IndexOptInfo *index)
237 List *clausegroup_list = NIL;
238 List *restrictinfo_list = index->rel->baserestrictinfo;
240 Oid *classes = index->classlist;
242 if (restrictinfo_list == NIL)
247 Oid curClass = classes[0];
248 List *clausegroup = NIL;
251 foreach(l, restrictinfo_list)
253 RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
255 if (match_clause_to_indexcol(index,
259 clausegroup = lappend(clausegroup, rinfo);
263 * If no clauses match this key, we're done; we don't want to look
264 * at keys to its right.
266 if (clausegroup == NIL)
269 clausegroup_list = lappend(clausegroup_list, clausegroup);
274 } while (!DoneMatchingIndexKeys(classes));
276 return clausegroup_list;
280 * group_clauses_by_indexkey_for_join
281 * Generate a list of sublists of clauses that can be used with an index
282 * to scan the inner side of a nestloop join.
284 * This is much like group_clauses_by_indexkey(), but we consider both
285 * join and restriction clauses. Any joinclause that uses only otherrels
286 * in the specified outer_relids is fair game. But there must be at least
287 * one such joinclause in the final list, otherwise we return NIL indicating
288 * that this index isn't interesting as an inner indexscan. (A scan using
289 * only restriction clauses shouldn't be created here, because a regular Path
290 * will already have been generated for it.)
293 group_clauses_by_indexkey_for_join(Query *root, IndexOptInfo *index,
295 JoinType jointype, bool isouterjoin)
297 List *clausegroup_list = NIL;
300 Oid *classes = index->classlist;
304 Oid curClass = classes[0];
305 List *clausegroup = NIL;
310 * We can always use plain restriction clauses for the rel. We
311 * scan these first because we want them first in the clausegroup
312 * list for the convenience of remove_redundant_join_clauses,
313 * which can never remove non-join clauses and hence won't be able
314 * to get rid of a non-join clause if it appears after a join
315 * clause it is redundant with.
317 foreach(l, index->rel->baserestrictinfo)
319 RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
321 /* Can't use pushed-down clauses in outer join */
322 if (isouterjoin && rinfo->is_pushed_down)
325 if (match_clause_to_indexcol(index,
329 clausegroup = lappend(clausegroup, rinfo);
332 /* found anything in base restrict list? */
333 numsources = (clausegroup != NIL) ? 1 : 0;
335 /* Look for joinclauses that are usable with given outer_relids */
336 foreach(l, index->rel->joininfo)
338 JoinInfo *joininfo = (JoinInfo *) lfirst(l);
339 bool jfoundhere = false;
342 if (!bms_is_subset(joininfo->unjoined_relids, outer_relids))
345 foreach(j, joininfo->jinfo_restrictinfo)
347 RestrictInfo *rinfo = (RestrictInfo *) lfirst(j);
349 /* Can't use pushed-down clauses in outer join */
350 if (isouterjoin && rinfo->is_pushed_down)
353 if (match_join_clause_to_indexcol(index,
358 clausegroup = lappend(clausegroup, rinfo);
370 * If we found clauses in more than one list, we may now have
371 * clauses that are known redundant. Get rid of 'em.
375 clausegroup = remove_redundant_join_clauses(root,
381 * If no clauses match this key, we're done; we don't want to look
382 * at keys to its right.
384 if (clausegroup == NIL)
387 clausegroup_list = lappend(clausegroup_list, clausegroup);
392 } while (!DoneMatchingIndexKeys(classes));
394 /* if no join clause was matched then forget it, per comments above */
398 return clausegroup_list;
403 * group_clauses_by_indexkey_for_or
404 * Generate a list of sublists of clauses that can be used with an index
405 * to find rows matching an OR subclause.
407 * This is essentially just like group_clauses_by_indexkey() except that
408 * we can use the given clause (or any AND subclauses of it) as well as
409 * top-level restriction clauses of the relation. Furthermore, we demand
410 * that at least one such use be made, otherwise we fail and return NIL.
411 * (Any path we made without such a use would be redundant with non-OR
412 * indexscans. Compare also group_clauses_by_indexkey_for_join.)
414 * XXX When we generate an indexqual list that uses both the OR subclause
415 * and top-level restriction clauses, we end up with a slightly inefficient
416 * plan because create_indexscan_plan is not very bright about figuring out
417 * which restriction clauses are implied by the generated indexqual condition.
418 * Currently we'll end up rechecking both the OR clause and the top-level
419 * restriction clause as qpquals. FIXME someday.
422 group_clauses_by_indexkey_for_or(IndexOptInfo *index, Expr *orsubclause)
424 List *clausegroup_list = NIL;
425 bool matched = false;
427 Oid *classes = index->classlist;
431 Oid curClass = classes[0];
432 List *clausegroup = NIL;
435 /* Try to match the OR subclause to the index key */
436 if (IsA(orsubclause, RestrictInfo))
438 if (match_clause_to_indexcol(index, indexcol, curClass,
439 (RestrictInfo *) orsubclause))
441 clausegroup = lappend(clausegroup, orsubclause);
445 else if (and_clause((Node *) orsubclause))
447 foreach(item, ((BoolExpr *) orsubclause)->args)
449 RestrictInfo *subsubclause = (RestrictInfo *) lfirst(item);
451 if (IsA(subsubclause, RestrictInfo) &&
452 match_clause_to_indexcol(index, indexcol, curClass,
455 clausegroup = lappend(clausegroup, subsubclause);
462 * If we found no clauses for this indexkey in the OR subclause
463 * itself, try looking in the rel's top-level restriction list.
465 * XXX should we always search the top-level list? Slower but could
466 * sometimes yield a better plan.
468 if (clausegroup == NIL)
470 foreach(item, index->rel->baserestrictinfo)
472 RestrictInfo *rinfo = (RestrictInfo *) lfirst(item);
474 if (match_clause_to_indexcol(index, indexcol, curClass,
476 clausegroup = lappend(clausegroup, rinfo);
481 * If still no clauses match this key, we're done; we don't want
482 * to look at keys to its right.
484 if (clausegroup == NIL)
487 clausegroup_list = lappend(clausegroup_list, clausegroup);
491 } while (!DoneMatchingIndexKeys(classes));
493 /* if OR clause was not used then forget it, per comments above */
497 return clausegroup_list;
502 * match_clause_to_indexcol()
503 * Determines whether a restriction clause matches a column of an index.
505 * To match a normal index, the clause:
507 * (1) must be in the form (indexkey op const) or (const op indexkey);
509 * (2) must contain an operator which is in the same class as the index
510 * operator for this column, or is a "special" operator as recognized
511 * by match_special_index_operator().
513 * Presently, the executor can only deal with indexquals that have the
514 * indexkey on the left, so we can only use clauses that have the indexkey
515 * on the right if we can commute the clause to put the key on the left.
516 * We do not actually do the commuting here, but we check whether a
517 * suitable commutator operator is available.
519 * For boolean indexes, it is also possible to match the clause directly
520 * to the indexkey; or perhaps the clause is (NOT indexkey).
522 * 'index' is the index of interest.
523 * 'indexcol' is a column number of 'index' (counting from 0).
524 * 'opclass' is the corresponding operator class.
525 * 'rinfo' is the clause to be tested (as a RestrictInfo node).
527 * Returns true if the clause can be used with this index key.
529 * NOTE: returns false if clause is an OR or AND clause; it is the
530 * responsibility of higher-level routines to cope with those.
533 match_clause_to_indexcol(IndexOptInfo *index,
538 Expr *clause = rinfo->clause;
542 /* First check for boolean-index cases. */
543 if (IsBooleanOpclass(opclass))
545 if (match_boolean_index_clause((Node *) clause, indexcol, index))
549 /* Else clause must be a binary opclause. */
550 if (!is_opclause(clause))
552 leftop = get_leftop(clause);
553 rightop = get_rightop(clause);
554 if (!leftop || !rightop)
558 * Check for clauses of the form: (indexkey operator constant) or
559 * (constant operator indexkey). Anything that is a "pseudo constant"
560 * expression will do.
562 if (match_index_to_operand(leftop, indexcol, index) &&
563 is_pseudo_constant_clause_relids(rightop, rinfo->right_relids))
565 if (is_indexable_operator(clause, opclass, true))
569 * If we didn't find a member of the index's opclass, see whether
570 * it is a "special" indexable operator.
572 if (match_special_index_operator(clause, opclass, true))
577 if (match_index_to_operand(rightop, indexcol, index) &&
578 is_pseudo_constant_clause_relids(leftop, rinfo->left_relids))
580 if (is_indexable_operator(clause, opclass, false))
584 * If we didn't find a member of the index's opclass, see whether
585 * it is a "special" indexable operator.
587 if (match_special_index_operator(clause, opclass, false))
596 * match_join_clause_to_indexcol()
597 * Determines whether a join clause matches a column of an index.
599 * To match, the clause:
601 * (1) must be in the form (indexkey op others) or (others op indexkey),
602 * where others is an expression involving only vars of the other
604 * (2) must contain an operator which is in the same class as the index
605 * operator for this column, or is a "special" operator as recognized
606 * by match_special_index_operator().
608 * The boolean-index cases don't apply.
610 * As above, we must be able to commute the clause to put the indexkey
613 * Note that we already know that the clause as a whole uses vars from
614 * the interesting set of relations. But we need to defend against
615 * expressions like (a.f1 OP (b.f2 OP a.f3)); that's not processable by
616 * an indexscan nestloop join, whereas (a.f1 OP (b.f2 OP c.f3)) is.
618 * 'index' is the index of interest.
619 * 'indexcol' is a column number of 'index' (counting from 0).
620 * 'opclass' is the corresponding operator class.
621 * 'rinfo' is the clause to be tested (as a RestrictInfo node).
623 * Returns true if the clause can be used with this index key.
625 * NOTE: returns false if clause is an OR or AND clause; it is the
626 * responsibility of higher-level routines to cope with those.
629 match_join_clause_to_indexcol(IndexOptInfo *index,
634 Expr *clause = rinfo->clause;
638 /* Clause must be a binary opclause. */
639 if (!is_opclause(clause))
641 leftop = get_leftop(clause);
642 rightop = get_rightop(clause);
643 if (!leftop || !rightop)
647 * Check for an indexqual that could be handled by a nestloop join. We
648 * need the index key to be compared against an expression that uses
649 * none of the indexed relation's vars and contains no volatile
652 if (match_index_to_operand(leftop, indexcol, index))
654 Relids othervarnos = rinfo->right_relids;
658 !bms_overlap(index->rel->relids, othervarnos) &&
659 !contain_volatile_functions(rightop) &&
660 is_indexable_operator(clause, opclass, true);
664 if (match_index_to_operand(rightop, indexcol, index))
666 Relids othervarnos = rinfo->left_relids;
670 !bms_overlap(index->rel->relids, othervarnos) &&
671 !contain_volatile_functions(leftop) &&
672 is_indexable_operator(clause, opclass, false);
681 * Does a binary opclause contain an operator matching the index opclass?
683 * If the indexkey is on the right, what we actually want to know
684 * is whether the operator has a commutator operator that matches
685 * the index's opclass.
687 * Returns the OID of the matching operator, or InvalidOid if no match.
688 * (Formerly, this routine might return a binary-compatible operator
689 * rather than the original one, but that kluge is history.)
692 indexable_operator(Expr *clause, Oid opclass, bool indexkey_on_left)
694 Oid expr_op = ((OpExpr *) clause)->opno;
697 /* Get the commuted operator if necessary */
698 if (indexkey_on_left)
699 commuted_op = expr_op;
701 commuted_op = get_commutator(expr_op);
702 if (commuted_op == InvalidOid)
705 /* OK if the (commuted) operator is a member of the index's opclass */
706 if (op_in_opclass(commuted_op, opclass))
712 /****************************************************************************
713 * ---- ROUTINES TO DO PARTIAL INDEX PREDICATE TESTS ----
714 ****************************************************************************/
717 * check_partial_indexes
718 * Check each partial index of the relation, and mark it predOK or not
719 * depending on whether the predicate is satisfied for this query.
722 check_partial_indexes(Query *root, RelOptInfo *rel)
724 List *restrictinfo_list = rel->baserestrictinfo;
727 foreach(ilist, rel->indexlist)
729 IndexOptInfo *index = (IndexOptInfo *) lfirst(ilist);
732 * If this is a partial index, we can only use it if it passes the
735 if (index->indpred == NIL)
736 continue; /* ignore non-partial indexes */
738 index->predOK = pred_test(index->indpred, restrictinfo_list);
744 * Does the "predicate inclusion test" for partial indexes.
746 * Recursively checks whether the clauses in restrictinfo_list imply
747 * that the given predicate is true.
749 * The top-level List structure of each list corresponds to an AND list.
750 * We assume that eval_const_expressions() has been applied and so there
751 * are no un-flattened ANDs or ORs (e.g., no AND immediately within an AND,
752 * including AND just below the top-level List structure).
753 * If this is not true we might fail to prove an implication that is
754 * valid, but no worse consequences will ensue.
757 pred_test(List *predicate_list, List *restrictinfo_list)
762 * Note: if Postgres tried to optimize queries by forming equivalence
763 * classes over equi-joined attributes (i.e., if it recognized that a
764 * qualification such as "where a.b=c.d and a.b=5" could make use of
765 * an index on c.d), then we could use that equivalence class info
766 * here with joininfo_list to do more complete tests for the usability
767 * of a partial index. For now, the test only uses restriction
768 * clauses (those in restrictinfo_list). --Nels, Dec '92
770 * XXX as of 7.1, equivalence class info *is* available. Consider
771 * improving this code as foreseen by Nels.
774 if (predicate_list == NIL)
775 return true; /* no predicate: the index is usable */
776 if (restrictinfo_list == NIL)
777 return false; /* no restriction clauses: the test must
781 * In all cases where the predicate is an AND-clause, pred_test_recurse()
782 * will prefer to iterate over the predicate's components. So we can
783 * just do that to start with here, and eliminate the need for
784 * pred_test_recurse() to handle a bare List on the predicate side.
786 * Logic is: restriction must imply each of the AND'ed predicate items.
788 foreach(item, predicate_list)
790 if (!pred_test_recurse((Node *) restrictinfo_list, lfirst(item)))
799 * Does the "predicate inclusion test" for non-NULL restriction and
802 * The logic followed here is ("=>" means "implies"):
803 * atom A => atom B iff: pred_test_simple_clause says so
804 * atom A => AND-expr B iff: A => each of B's components
805 * atom A => OR-expr B iff: A => any of B's components
806 * AND-expr A => atom B iff: any of A's components => B
807 * AND-expr A => AND-expr B iff: A => each of B's components
808 * AND-expr A => OR-expr B iff: A => any of B's components,
809 * *or* any of A's components => B
810 * OR-expr A => atom B iff: each of A's components => B
811 * OR-expr A => AND-expr B iff: A => each of B's components
812 * OR-expr A => OR-expr B iff: each of A's components => any of B's
814 * An "atom" is anything other than an AND or OR node. Notice that we don't
815 * have any special logic to handle NOT nodes; these should have been pushed
816 * down or eliminated where feasible by prepqual.c.
818 * We can't recursively expand either side first, but have to interleave
819 * the expansions per the above rules, to be sure we handle all of these
821 * (x OR y) => (x OR y OR z)
822 * (x AND y AND z) => (x AND y)
823 * (x AND y) => ((x AND y) OR z)
824 * ((x OR y) AND z) => (x OR y)
825 * This is still not an exhaustive test, but it handles most normal cases
826 * under the assumption that both inputs have been AND/OR flattened.
828 * A bare List node on the restriction side is interpreted as an AND clause,
829 * in order to handle the top-level restriction List properly. However we
830 * need not consider a List on the predicate side since pred_test() already
833 * We have to be prepared to handle RestrictInfo nodes in the restrictinfo
834 * tree, though not in the predicate tree.
838 pred_test_recurse(Node *clause, Node *predicate)
842 Assert(clause != NULL);
843 /* skip through RestrictInfo */
844 if (IsA(clause, RestrictInfo))
846 clause = (Node *) ((RestrictInfo *) clause)->clause;
847 Assert(clause != NULL);
848 Assert(!IsA(clause, RestrictInfo));
850 Assert(predicate != NULL);
853 * Since a restriction List clause is handled the same as an AND clause,
854 * we can avoid duplicate code like this:
856 if (and_clause(clause))
857 clause = (Node *) ((BoolExpr *) clause)->args;
859 if (IsA(clause, List))
861 if (and_clause(predicate))
863 /* AND-clause => AND-clause if A implies each of B's items */
864 foreach(item, ((BoolExpr *) predicate)->args)
866 if (!pred_test_recurse(clause, lfirst(item)))
871 else if (or_clause(predicate))
873 /* AND-clause => OR-clause if A implies any of B's items */
874 /* Needed to handle (x AND y) => ((x AND y) OR z) */
875 foreach(item, ((BoolExpr *) predicate)->args)
877 if (pred_test_recurse(clause, lfirst(item)))
880 /* Also check if any of A's items implies B */
881 /* Needed to handle ((x OR y) AND z) => (x OR y) */
882 foreach(item, (List *) clause)
884 if (pred_test_recurse(lfirst(item), predicate))
891 /* AND-clause => atom if any of A's items implies B */
892 foreach(item, (List *) clause)
894 if (pred_test_recurse(lfirst(item), predicate))
900 else if (or_clause(clause))
902 if (or_clause(predicate))
905 * OR-clause => OR-clause if each of A's items implies any of
906 * B's items. Messy but can't do it any more simply.
908 foreach(item, ((BoolExpr *) clause)->args)
910 Node *citem = lfirst(item);
913 foreach(item2, ((BoolExpr *) predicate)->args)
915 if (pred_test_recurse(citem, lfirst(item2)))
919 return false; /* doesn't imply any of B's */
925 /* OR-clause => AND-clause if each of A's items implies B */
926 /* OR-clause => atom if each of A's items implies B */
927 foreach(item, ((BoolExpr *) clause)->args)
929 if (!pred_test_recurse(lfirst(item), predicate))
937 if (and_clause(predicate))
939 /* atom => AND-clause if A implies each of B's items */
940 foreach(item, ((BoolExpr *) predicate)->args)
942 if (!pred_test_recurse(clause, lfirst(item)))
947 else if (or_clause(predicate))
949 /* atom => OR-clause if A implies any of B's items */
950 foreach(item, ((BoolExpr *) predicate)->args)
952 if (pred_test_recurse(clause, lfirst(item)))
959 /* atom => atom is the base case */
960 return pred_test_simple_clause((Expr *) predicate, clause);
967 * Define an "operator implication table" for btree operators ("strategies").
969 * The strategy numbers defined by btree indexes (see access/skey.h) are:
970 * (1) < (2) <= (3) = (4) >= (5) >
971 * and in addition we use (6) to represent <>. <> is not a btree-indexable
972 * operator, but we assume here that if the equality operator of a btree
973 * opclass has a negator operator, the negator behaves as <> for the opclass.
975 * The interpretation of:
977 * test_op = BT_implic_table[given_op-1][target_op-1]
979 * where test_op, given_op and target_op are strategy numbers (from 1 to 6)
980 * of btree operators, is as follows:
982 * If you know, for some ATTR, that "ATTR given_op CONST1" is true, and you
983 * want to determine whether "ATTR target_op CONST2" must also be true, then
984 * you can use "CONST2 test_op CONST1" as a test. If this test returns true,
985 * then the target expression must be true; if the test returns false, then
986 * the target expression may be false.
988 * An entry where test_op == 0 means the implication cannot be determined,
989 * i.e., this test should always be considered false.
992 #define BTLT BTLessStrategyNumber
993 #define BTLE BTLessEqualStrategyNumber
994 #define BTEQ BTEqualStrategyNumber
995 #define BTGE BTGreaterEqualStrategyNumber
996 #define BTGT BTGreaterStrategyNumber
999 static const StrategyNumber
1000 BT_implic_table[6][6] = {
1002 * The target operator:
1006 {BTGE, BTGE, 0, 0, 0, BTGE}, /* LT */
1007 {BTGT, BTGE, 0, 0, 0, BTGT}, /* LE */
1008 {BTGT, BTGE, BTEQ, BTLE, BTLT, BTNE}, /* EQ */
1009 {0, 0, 0, BTLE, BTLT, BTLT}, /* GE */
1010 {0, 0, 0, BTLE, BTLE, BTLE}, /* GT */
1011 {0, 0, 0, 0, 0, BTEQ} /* NE */
1016 * pred_test_simple_clause
1017 * Does the "predicate inclusion test" for a "simple clause" predicate
1018 * and a "simple clause" restriction.
1020 * We have three strategies for determining whether one simple clause
1023 * A simple and general way is to see if they are equal(); this works for any
1024 * kind of expression. (Actually, there is an implied assumption that the
1025 * functions in the expression are immutable, ie dependent only on their input
1026 * arguments --- but this was checked for the predicate by CheckPredicate().)
1028 * When the predicate is of the form "foo IS NOT NULL", we can conclude that
1029 * the predicate is implied if the clause is a strict operator or function
1030 * that has "foo" as an input. In this case the clause must yield NULL when
1031 * "foo" is NULL, which we can take as equivalent to FALSE because we know
1032 * we are within an AND/OR subtree of a WHERE clause. (Again, "foo" is
1033 * already known immutable, so the clause will certainly always fail.)
1035 * Our other way works only for binary boolean opclauses of the form
1036 * "foo op constant", where "foo" is the same in both clauses. The operators
1037 * and constants can be different but the operators must be in the same btree
1038 * operator class. We use the above operator implication table to be able to
1039 * derive implications between nonidentical clauses. (Note: "foo" is known
1040 * immutable, and constants are surely immutable, but we have to check that
1041 * the operators are too. As of 8.0 it's possible for opclasses to contain
1042 * operators that are merely stable, and we dare not make deductions with
1045 * Eventually, rtree operators could also be handled by defining an
1046 * appropriate "RT_implic_table" array.
1050 pred_test_simple_clause(Expr *predicate, Node *clause)
1058 bool pred_var_on_left,
1065 test_op = InvalidOid;
1068 StrategyNumber pred_strategy,
1073 ExprState *test_exprstate;
1079 MemoryContext oldcontext;
1081 /* First try the equal() test */
1082 if (equal((Node *) predicate, clause))
1085 /* Next try the IS NOT NULL case */
1086 if (predicate && IsA(predicate, NullTest) &&
1087 ((NullTest *) predicate)->nulltesttype == IS_NOT_NULL)
1089 Expr *nonnullarg = ((NullTest *) predicate)->arg;
1091 if (is_opclause(clause) &&
1092 list_member(((OpExpr *) clause)->args, nonnullarg) &&
1093 op_strict(((OpExpr *) clause)->opno))
1095 if (is_funcclause(clause) &&
1096 list_member(((FuncExpr *) clause)->args, nonnullarg) &&
1097 func_strict(((FuncExpr *) clause)->funcid))
1099 return false; /* we can't succeed below... */
1103 * Can't do anything more unless they are both binary opclauses with a
1104 * Const on one side, and identical subexpressions on the other sides.
1105 * Note we don't have to think about binary relabeling of the Const
1106 * node, since that would have been folded right into the Const.
1108 * If either Const is null, we also fail right away; this assumes that
1109 * the test operator will always be strict.
1111 if (!is_opclause(predicate))
1113 leftop = get_leftop(predicate);
1114 rightop = get_rightop(predicate);
1115 if (rightop == NULL)
1116 return false; /* not a binary opclause */
1117 if (IsA(rightop, Const))
1120 pred_const = (Const *) rightop;
1121 pred_var_on_left = true;
1123 else if (IsA(leftop, Const))
1126 pred_const = (Const *) leftop;
1127 pred_var_on_left = false;
1130 return false; /* no Const to be found */
1131 if (pred_const->constisnull)
1134 if (!is_opclause(clause))
1136 leftop = get_leftop((Expr *) clause);
1137 rightop = get_rightop((Expr *) clause);
1138 if (rightop == NULL)
1139 return false; /* not a binary opclause */
1140 if (IsA(rightop, Const))
1142 clause_var = leftop;
1143 clause_const = (Const *) rightop;
1144 clause_var_on_left = true;
1146 else if (IsA(leftop, Const))
1148 clause_var = rightop;
1149 clause_const = (Const *) leftop;
1150 clause_var_on_left = false;
1153 return false; /* no Const to be found */
1154 if (clause_const->constisnull)
1158 * Check for matching subexpressions on the non-Const sides. We used
1159 * to only allow a simple Var, but it's about as easy to allow any
1160 * expression. Remember we already know that the pred expression does
1161 * not contain any non-immutable functions, so identical expressions
1162 * should yield identical results.
1164 if (!equal(pred_var, clause_var))
1168 * Okay, get the operators in the two clauses we're comparing. Commute
1169 * them if needed so that we can assume the variables are on the left.
1171 pred_op = ((OpExpr *) predicate)->opno;
1172 if (!pred_var_on_left)
1174 pred_op = get_commutator(pred_op);
1175 if (!OidIsValid(pred_op))
1179 clause_op = ((OpExpr *) clause)->opno;
1180 if (!clause_var_on_left)
1182 clause_op = get_commutator(clause_op);
1183 if (!OidIsValid(clause_op))
1188 * Try to find a btree opclass containing the needed operators.
1190 * We must find a btree opclass that contains both operators, else the
1191 * implication can't be determined. Also, the pred_op has to be of
1192 * default subtype (implying left and right input datatypes are the
1193 * same); otherwise it's unsafe to put the pred_const on the left side
1194 * of the test. Also, the opclass must contain a suitable test
1195 * operator matching the clause_const's type (which we take to mean
1196 * that it has the same subtype as the original clause_operator).
1198 * If there are multiple matching opclasses, assume we can use any one to
1199 * determine the logical relationship of the two operators and the
1200 * correct corresponding test operator. This should work for any
1201 * logically consistent opclasses.
1203 catlist = SearchSysCacheList(AMOPOPID, 1,
1204 ObjectIdGetDatum(pred_op),
1208 * If we couldn't find any opclass containing the pred_op, perhaps it
1209 * is a <> operator. See if it has a negator that is in an opclass.
1211 pred_op_negated = false;
1212 if (catlist->n_members == 0)
1214 pred_op_negator = get_negator(pred_op);
1215 if (OidIsValid(pred_op_negator))
1217 pred_op_negated = true;
1218 ReleaseSysCacheList(catlist);
1219 catlist = SearchSysCacheList(AMOPOPID, 1,
1220 ObjectIdGetDatum(pred_op_negator),
1225 /* Also may need the clause_op's negator */
1226 clause_op_negator = get_negator(clause_op);
1228 /* Now search the opclasses */
1229 for (i = 0; i < catlist->n_members; i++)
1231 HeapTuple pred_tuple = &catlist->members[i]->tuple;
1232 Form_pg_amop pred_form = (Form_pg_amop) GETSTRUCT(pred_tuple);
1233 HeapTuple clause_tuple;
1235 opclass_id = pred_form->amopclaid;
1238 if (!opclass_is_btree(opclass_id))
1240 /* predicate operator must be default within this opclass */
1241 if (pred_form->amopsubtype != InvalidOid)
1244 /* Get the predicate operator's btree strategy number */
1245 pred_strategy = (StrategyNumber) pred_form->amopstrategy;
1246 Assert(pred_strategy >= 1 && pred_strategy <= 5);
1248 if (pred_op_negated)
1250 /* Only consider negators that are = */
1251 if (pred_strategy != BTEqualStrategyNumber)
1253 pred_strategy = BTNE;
1257 * From the same opclass, find a strategy number for the
1258 * clause_op, if possible
1260 clause_tuple = SearchSysCache(AMOPOPID,
1261 ObjectIdGetDatum(clause_op),
1262 ObjectIdGetDatum(opclass_id),
1264 if (HeapTupleIsValid(clause_tuple))
1266 Form_pg_amop clause_form = (Form_pg_amop) GETSTRUCT(clause_tuple);
1268 /* Get the restriction clause operator's strategy/subtype */
1269 clause_strategy = (StrategyNumber) clause_form->amopstrategy;
1270 Assert(clause_strategy >= 1 && clause_strategy <= 5);
1271 clause_subtype = clause_form->amopsubtype;
1272 ReleaseSysCache(clause_tuple);
1274 else if (OidIsValid(clause_op_negator))
1276 clause_tuple = SearchSysCache(AMOPOPID,
1277 ObjectIdGetDatum(clause_op_negator),
1278 ObjectIdGetDatum(opclass_id),
1280 if (HeapTupleIsValid(clause_tuple))
1282 Form_pg_amop clause_form = (Form_pg_amop) GETSTRUCT(clause_tuple);
1284 /* Get the restriction clause operator's strategy/subtype */
1285 clause_strategy = (StrategyNumber) clause_form->amopstrategy;
1286 Assert(clause_strategy >= 1 && clause_strategy <= 5);
1287 clause_subtype = clause_form->amopsubtype;
1288 ReleaseSysCache(clause_tuple);
1290 /* Only consider negators that are = */
1291 if (clause_strategy != BTEqualStrategyNumber)
1293 clause_strategy = BTNE;
1302 * Look up the "test" strategy number in the implication table
1304 test_strategy = BT_implic_table[clause_strategy - 1][pred_strategy - 1];
1305 if (test_strategy == 0)
1307 /* Can't determine implication using this interpretation */
1312 * See if opclass has an operator for the test strategy and the
1315 if (test_strategy == BTNE)
1317 test_op = get_opclass_member(opclass_id, clause_subtype,
1318 BTEqualStrategyNumber);
1319 if (OidIsValid(test_op))
1320 test_op = get_negator(test_op);
1324 test_op = get_opclass_member(opclass_id, clause_subtype,
1327 if (OidIsValid(test_op))
1330 * Last check: test_op must be immutable.
1332 * Note that we require only the test_op to be immutable, not the
1333 * original clause_op. (pred_op must be immutable, else it
1334 * would not be allowed in an index predicate.) Essentially
1335 * we are assuming that the opclass is consistent even if it
1336 * contains operators that are merely stable.
1338 if (op_volatile(test_op) == PROVOLATILE_IMMUTABLE)
1346 ReleaseSysCacheList(catlist);
1350 /* couldn't find a btree opclass to interpret the operators */
1355 * Evaluate the test. For this we need an EState.
1357 estate = CreateExecutorState();
1359 /* We can use the estate's working context to avoid memory leaks. */
1360 oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
1362 /* Build expression tree */
1363 test_expr = make_opclause(test_op,
1366 (Expr *) pred_const,
1367 (Expr *) clause_const);
1369 /* Prepare it for execution */
1370 test_exprstate = ExecPrepareExpr(test_expr, estate);
1372 /* And execute it. */
1373 test_result = ExecEvalExprSwitchContext(test_exprstate,
1374 GetPerTupleExprContext(estate),
1377 /* Get back to outer memory context */
1378 MemoryContextSwitchTo(oldcontext);
1380 /* Release all the junk we just created */
1381 FreeExecutorState(estate);
1385 /* Treat a null result as false ... but it's a tad fishy ... */
1386 elog(DEBUG2, "null predicate test result");
1389 return DatumGetBool(test_result);
1393 /****************************************************************************
1394 * ---- ROUTINES TO CHECK JOIN CLAUSES ----
1395 ****************************************************************************/
1398 * indexable_outerrelids
1399 * Finds all other relids that participate in any indexable join clause
1400 * for the specified index. Returns a set of relids.
1403 indexable_outerrelids(IndexOptInfo *index)
1405 Relids outer_relids = NULL;
1408 foreach(l, index->rel->joininfo)
1410 JoinInfo *joininfo = (JoinInfo *) lfirst(l);
1411 bool match_found = false;
1415 * Examine each joinclause in the JoinInfo node's list to see if
1416 * it matches any key of the index. If so, add the JoinInfo's
1417 * otherrels to the result. We can skip examining other
1418 * joinclauses in the same list as soon as we find a match (since
1419 * by definition they all have the same otherrels).
1421 foreach(j, joininfo->jinfo_restrictinfo)
1423 RestrictInfo *rinfo = (RestrictInfo *) lfirst(j);
1425 Oid *classes = index->classlist;
1429 Oid curClass = classes[0];
1431 if (match_join_clause_to_indexcol(index,
1443 } while (!DoneMatchingIndexKeys(classes));
1451 outer_relids = bms_add_members(outer_relids,
1452 joininfo->unjoined_relids);
1456 return outer_relids;
1460 * best_inner_indexscan
1461 * Finds the best available inner indexscan for a nestloop join
1462 * with the given rel on the inside and the given outer_relids outside.
1463 * May return NULL if there are no possible inner indexscans.
1465 * We ignore ordering considerations (since a nestloop's inner scan's order
1466 * is uninteresting). Also, we consider only total cost when deciding which
1467 * of two possible paths is better --- this assumes that all indexpaths have
1468 * negligible startup cost. (True today, but someday we might have to think
1469 * harder.) Therefore, there is only one dimension of comparison and so it's
1470 * sufficient to return a single "best" path.
1473 best_inner_indexscan(Query *root, RelOptInfo *rel,
1474 Relids outer_relids, JoinType jointype)
1476 Path *cheapest = NULL;
1480 InnerIndexscanInfo *info;
1481 MemoryContext oldcontext;
1484 * Nestloop only supports inner, left, and IN joins.
1490 case JOIN_UNIQUE_OUTER:
1491 isouterjoin = false;
1501 * If there are no indexable joinclauses for this rel, exit quickly.
1503 if (bms_is_empty(rel->index_outer_relids))
1507 * Otherwise, we have to do path selection in the memory context of
1508 * the given rel, so that any created path can be safely attached to
1509 * the rel's cache of best inner paths. (This is not currently an
1510 * issue for normal planning, but it is an issue for GEQO planning.)
1512 oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(rel));
1515 * Intersect the given outer_relids with index_outer_relids to find
1516 * the set of outer relids actually relevant for this index. If there
1517 * are none, again we can fail immediately.
1519 outer_relids = bms_intersect(rel->index_outer_relids, outer_relids);
1520 if (bms_is_empty(outer_relids))
1522 bms_free(outer_relids);
1523 MemoryContextSwitchTo(oldcontext);
1528 * Look to see if we already computed the result for this set of
1529 * relevant outerrels. (We include the isouterjoin status in the
1530 * cache lookup key for safety. In practice I suspect this is not
1531 * necessary because it should always be the same for a given
1534 foreach(jlist, rel->index_inner_paths)
1536 info = (InnerIndexscanInfo *) lfirst(jlist);
1537 if (bms_equal(info->other_relids, outer_relids) &&
1538 info->isouterjoin == isouterjoin)
1540 bms_free(outer_relids);
1541 MemoryContextSwitchTo(oldcontext);
1542 return info->best_innerpath;
1547 * For each index of the rel, find the best path; then choose the best
1548 * overall. We cache the per-index results as well as the overall
1549 * result. (This is useful because different indexes may have
1550 * different relevant outerrel sets, so different overall outerrel
1551 * sets might still map to the same computation for a given index.)
1553 foreach(ilist, rel->indexlist)
1555 IndexOptInfo *index = (IndexOptInfo *) lfirst(ilist);
1556 Relids index_outer_relids;
1559 /* identify set of relevant outer relids for this index */
1560 index_outer_relids = bms_intersect(index->outer_relids, outer_relids);
1562 if (bms_is_empty(index_outer_relids))
1564 bms_free(index_outer_relids);
1569 * Look to see if we already computed the result for this index.
1571 foreach(jlist, index->inner_paths)
1573 info = (InnerIndexscanInfo *) lfirst(jlist);
1574 if (bms_equal(info->other_relids, index_outer_relids) &&
1575 info->isouterjoin == isouterjoin)
1577 path = info->best_innerpath;
1578 bms_free(index_outer_relids); /* not needed anymore */
1583 if (jlist == NULL) /* failed to find a match? */
1587 /* find useful clauses for this index and outerjoin set */
1588 clausegroups = group_clauses_by_indexkey_for_join(root,
1596 path = make_innerjoin_index_path(root, index, clausegroups);
1599 /* Cache the result --- whether positive or negative */
1600 info = makeNode(InnerIndexscanInfo);
1601 info->other_relids = index_outer_relids;
1602 info->isouterjoin = isouterjoin;
1603 info->best_innerpath = path;
1604 index->inner_paths = lcons(info, index->inner_paths);
1608 (cheapest == NULL ||
1609 compare_path_costs(path, cheapest, TOTAL_COST) < 0))
1613 /* Cache the result --- whether positive or negative */
1614 info = makeNode(InnerIndexscanInfo);
1615 info->other_relids = outer_relids;
1616 info->isouterjoin = isouterjoin;
1617 info->best_innerpath = cheapest;
1618 rel->index_inner_paths = lcons(info, rel->index_inner_paths);
1620 MemoryContextSwitchTo(oldcontext);
1625 /****************************************************************************
1626 * ---- PATH CREATION UTILITIES ----
1627 ****************************************************************************/
1630 * make_innerjoin_index_path
1631 * Create an index path node for a path to be used as an inner
1632 * relation in a nestloop join.
1634 * 'index' is the index of interest
1635 * 'clausegroups' is a list of lists of RestrictInfos that can use 'index'
1638 make_innerjoin_index_path(Query *root,
1639 IndexOptInfo *index,
1642 IndexPath *pathnode = makeNode(IndexPath);
1643 RelOptInfo *rel = index->rel;
1647 /* XXX perhaps this code should be merged with create_index_path? */
1649 pathnode->path.pathtype = T_IndexScan;
1650 pathnode->path.parent = rel;
1653 * There's no point in marking the path with any pathkeys, since it
1654 * will only ever be used as the inner path of a nestloop, and so its
1655 * ordering does not matter.
1657 pathnode->path.pathkeys = NIL;
1659 /* Convert clauses to indexquals the executor can handle */
1660 indexquals = expand_indexqual_conditions(index, clausegroups);
1662 /* Flatten the clausegroups list to produce indexclauses list */
1663 allclauses = flatten_clausegroups_list(clausegroups);
1666 * Note that we are making a pathnode for a single-scan indexscan;
1667 * therefore, indexinfo etc should be single-element lists.
1669 pathnode->indexinfo = list_make1(index);
1670 pathnode->indexclauses = list_make1(allclauses);
1671 pathnode->indexquals = list_make1(indexquals);
1673 pathnode->isjoininner = true;
1675 /* We don't actually care what order the index scans in ... */
1676 pathnode->indexscandir = NoMovementScanDirection;
1679 * We must compute the estimated number of output rows for the
1680 * indexscan. This is less than rel->rows because of the additional
1681 * selectivity of the join clauses. Since clausegroups may contain
1682 * both restriction and join clauses, we have to do a set union to get
1683 * the full set of clauses that must be considered to compute the
1684 * correct selectivity. (Without the union operation, we might have
1685 * some restriction clauses appearing twice, which'd mislead
1686 * clauselist_selectivity into double-counting their selectivity.
1687 * However, since RestrictInfo nodes aren't copied when linking them
1688 * into different lists, it should be sufficient to use pointer
1689 * comparison to remove duplicates.)
1691 * Always assume the join type is JOIN_INNER; even if some of the join
1692 * clauses come from other contexts, that's not our problem.
1694 allclauses = list_union_ptr(rel->baserestrictinfo, allclauses);
1695 pathnode->rows = rel->tuples *
1696 clauselist_selectivity(root,
1698 rel->relid, /* do not use 0! */
1700 /* Like costsize.c, force estimate to be at least one row */
1701 pathnode->rows = clamp_row_est(pathnode->rows);
1703 cost_index(&pathnode->path, root, index, indexquals, true);
1705 return (Path *) pathnode;
1709 * flatten_clausegroups_list
1710 * Given a list of lists of RestrictInfos, flatten it to a list
1713 * This is used to flatten out the result of group_clauses_by_indexkey()
1714 * or one of its sibling routines, to produce an indexclauses list.
1717 flatten_clausegroups_list(List *clausegroups)
1719 List *allclauses = NIL;
1722 foreach(l, clausegroups)
1723 allclauses = list_concat(allclauses, list_copy((List *) lfirst(l)));
1728 * make_expr_from_indexclauses()
1729 * Given an indexclauses structure, produce an ordinary boolean expression.
1731 * This consists of stripping out the RestrictInfo nodes and inserting
1732 * explicit AND and OR nodes as needed. There's not much to it, but
1733 * the functionality is needed in a few places, so centralize the logic.
1736 make_expr_from_indexclauses(List *indexclauses)
1738 List *orclauses = NIL;
1741 /* There's no such thing as an indexpath with zero scans */
1742 Assert(indexclauses != NIL);
1744 foreach(orlist, indexclauses)
1746 List *andlist = (List *) lfirst(orlist);
1748 /* Strip RestrictInfos */
1749 andlist = get_actual_clauses(andlist);
1750 /* Insert AND node if needed, and add to orclauses list */
1751 orclauses = lappend(orclauses, make_ands_explicit(andlist));
1754 if (list_length(orclauses) > 1)
1755 return make_orclause(orclauses);
1757 return (Expr *) linitial(orclauses);
1761 /****************************************************************************
1762 * ---- ROUTINES TO CHECK OPERANDS ----
1763 ****************************************************************************/
1766 * match_index_to_operand()
1767 * Generalized test for a match between an index's key
1768 * and the operand on one side of a restriction or join clause.
1770 * operand: the nodetree to be compared to the index
1771 * indexcol: the column number of the index (counting from 0)
1772 * index: the index of interest
1775 match_index_to_operand(Node *operand,
1777 IndexOptInfo *index)
1782 * Ignore any RelabelType node above the operand. This is needed to
1783 * be able to apply indexscanning in binary-compatible-operator cases.
1784 * Note: we can assume there is at most one RelabelType node;
1785 * eval_const_expressions() will have simplified if more than one.
1787 if (operand && IsA(operand, RelabelType))
1788 operand = (Node *) ((RelabelType *) operand)->arg;
1790 indkey = index->indexkeys[indexcol];
1794 * Simple index column; operand must be a matching Var.
1796 if (operand && IsA(operand, Var) &&
1797 index->rel->relid == ((Var *) operand)->varno &&
1798 indkey == ((Var *) operand)->varattno)
1804 * Index expression; find the correct expression. (This search
1805 * could be avoided, at the cost of complicating all the callers
1806 * of this routine; doesn't seem worth it.)
1808 ListCell *indexpr_item;
1812 indexpr_item = list_head(index->indexprs);
1813 for (i = 0; i < indexcol; i++)
1815 if (index->indexkeys[i] == 0)
1817 if (indexpr_item == NULL)
1818 elog(ERROR, "wrong number of index expressions");
1819 indexpr_item = lnext(indexpr_item);
1822 if (indexpr_item == NULL)
1823 elog(ERROR, "wrong number of index expressions");
1824 indexkey = (Node *) lfirst(indexpr_item);
1827 * Does it match the operand? Again, strip any relabeling.
1829 if (indexkey && IsA(indexkey, RelabelType))
1830 indexkey = (Node *) ((RelabelType *) indexkey)->arg;
1832 if (equal(indexkey, operand))
1839 /****************************************************************************
1840 * ---- ROUTINES FOR "SPECIAL" INDEXABLE OPERATORS ----
1841 ****************************************************************************/
1844 * These routines handle special optimization of operators that can be
1845 * used with index scans even though they are not known to the executor's
1846 * indexscan machinery. The key idea is that these operators allow us
1847 * to derive approximate indexscan qual clauses, such that any tuples
1848 * that pass the operator clause itself must also satisfy the simpler
1849 * indexscan condition(s). Then we can use the indexscan machinery
1850 * to avoid scanning as much of the table as we'd otherwise have to,
1851 * while applying the original operator as a qpqual condition to ensure
1852 * we deliver only the tuples we want. (In essence, we're using a regular
1853 * index as if it were a lossy index.)
1855 * An example of what we're doing is
1856 * textfield LIKE 'abc%'
1857 * from which we can generate the indexscanable conditions
1858 * textfield >= 'abc' AND textfield < 'abd'
1859 * which allow efficient scanning of an index on textfield.
1860 * (In reality, character set and collation issues make the transformation
1861 * from LIKE to indexscan limits rather harder than one might think ...
1862 * but that's the basic idea.)
1864 * Another thing that we do with this machinery is to provide special
1865 * smarts for "boolean" indexes (that is, indexes on boolean columns
1866 * that support boolean equality). We can transform a plain reference
1867 * to the indexkey into "indexkey = true", or "NOT indexkey" into
1868 * "indexkey = false", so as to make the expression indexable using the
1869 * regular index operators. (As of Postgres 8.1, we must do this here
1870 * because constant simplification does the reverse transformation;
1871 * without this code there'd be no way to use such an index at all.)
1873 * Three routines are provided here:
1875 * match_special_index_operator() is just an auxiliary function for
1876 * match_clause_to_indexcol(); after the latter fails to recognize a
1877 * restriction opclause's operator as a member of an index's opclass,
1878 * it asks match_special_index_operator() whether the clause should be
1879 * considered an indexqual anyway.
1881 * match_boolean_index_clause() similarly detects clauses that can be
1882 * converted into boolean equality operators.
1884 * expand_indexqual_conditions() converts a list of lists of RestrictInfo
1885 * nodes (with implicit AND semantics across list elements) into
1886 * a list of clauses that the executor can actually handle. For operators
1887 * that are members of the index's opclass this transformation is a no-op,
1888 * but clauses recognized by match_special_index_operator() or
1889 * match_boolean_index_clause() must be converted into one or more "regular"
1890 * indexqual conditions.
1895 * match_boolean_index_clause
1896 * Recognize restriction clauses that can be matched to a boolean index.
1898 * This should be called only when IsBooleanOpclass() recognizes the
1899 * index's operator class. We check to see if the clause matches the
1903 match_boolean_index_clause(Node *clause,
1905 IndexOptInfo *index)
1908 if (match_index_to_operand(clause, indexcol, index))
1911 if (not_clause(clause))
1913 if (match_index_to_operand((Node *) get_notclausearg((Expr *) clause),
1918 * Since we only consider clauses at top level of WHERE, we can convert
1919 * indexkey IS TRUE and indexkey IS FALSE to index searches as well.
1920 * The different meaning for NULL isn't important.
1922 else if (clause && IsA(clause, BooleanTest))
1924 BooleanTest *btest = (BooleanTest *) clause;
1926 if (btest->booltesttype == IS_TRUE ||
1927 btest->booltesttype == IS_FALSE)
1928 if (match_index_to_operand((Node *) btest->arg,
1936 * match_special_index_operator
1937 * Recognize restriction clauses that can be used to generate
1938 * additional indexscanable qualifications.
1940 * The given clause is already known to be a binary opclause having
1941 * the form (indexkey OP pseudoconst) or (pseudoconst OP indexkey),
1942 * but the OP proved not to be one of the index's opclass operators.
1943 * Return 'true' if we can do something with it anyway.
1946 match_special_index_operator(Expr *clause, Oid opclass,
1947 bool indexkey_on_left)
1949 bool isIndexable = false;
1953 Const *prefix = NULL;
1957 * Currently, all known special operators require the indexkey on the
1958 * left, but this test could be pushed into the switch statement if
1959 * some are added that do not...
1961 if (!indexkey_on_left)
1964 /* we know these will succeed */
1965 rightop = get_rightop(clause);
1966 expr_op = ((OpExpr *) clause)->opno;
1968 /* again, required for all current special ops: */
1969 if (!IsA(rightop, Const) ||
1970 ((Const *) rightop)->constisnull)
1972 patt = (Const *) rightop;
1976 case OID_TEXT_LIKE_OP:
1977 case OID_BPCHAR_LIKE_OP:
1978 case OID_NAME_LIKE_OP:
1979 /* the right-hand const is type text for all of these */
1980 isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like,
1981 &prefix, &rest) != Pattern_Prefix_None;
1984 case OID_BYTEA_LIKE_OP:
1985 isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like,
1986 &prefix, &rest) != Pattern_Prefix_None;
1989 case OID_TEXT_ICLIKE_OP:
1990 case OID_BPCHAR_ICLIKE_OP:
1991 case OID_NAME_ICLIKE_OP:
1992 /* the right-hand const is type text for all of these */
1993 isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC,
1994 &prefix, &rest) != Pattern_Prefix_None;
1997 case OID_TEXT_REGEXEQ_OP:
1998 case OID_BPCHAR_REGEXEQ_OP:
1999 case OID_NAME_REGEXEQ_OP:
2000 /* the right-hand const is type text for all of these */
2001 isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex,
2002 &prefix, &rest) != Pattern_Prefix_None;
2005 case OID_TEXT_ICREGEXEQ_OP:
2006 case OID_BPCHAR_ICREGEXEQ_OP:
2007 case OID_NAME_ICREGEXEQ_OP:
2008 /* the right-hand const is type text for all of these */
2009 isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
2010 &prefix, &rest) != Pattern_Prefix_None;
2013 case OID_INET_SUB_OP:
2014 case OID_INET_SUBEQ_OP:
2015 case OID_CIDR_SUB_OP:
2016 case OID_CIDR_SUBEQ_OP:
2023 pfree(DatumGetPointer(prefix->constvalue));
2027 /* done if the expression doesn't look indexable */
2032 * Must also check that index's opclass supports the operators we will
2033 * want to apply. (A hash index, for example, will not support ">=".)
2034 * Currently, only btree supports the operators we need.
2036 * We insist on the opclass being the specific one we expect, else we'd
2037 * do the wrong thing if someone were to make a reverse-sort opclass
2038 * with the same operators.
2042 case OID_TEXT_LIKE_OP:
2043 case OID_TEXT_ICLIKE_OP:
2044 case OID_TEXT_REGEXEQ_OP:
2045 case OID_TEXT_ICREGEXEQ_OP:
2046 /* text operators will be used for varchar inputs, too */
2048 (opclass == TEXT_PATTERN_BTREE_OPS_OID) ||
2049 (opclass == TEXT_BTREE_OPS_OID && lc_collate_is_c()) ||
2050 (opclass == VARCHAR_PATTERN_BTREE_OPS_OID) ||
2051 (opclass == VARCHAR_BTREE_OPS_OID && lc_collate_is_c());
2054 case OID_BPCHAR_LIKE_OP:
2055 case OID_BPCHAR_ICLIKE_OP:
2056 case OID_BPCHAR_REGEXEQ_OP:
2057 case OID_BPCHAR_ICREGEXEQ_OP:
2059 (opclass == BPCHAR_PATTERN_BTREE_OPS_OID) ||
2060 (opclass == BPCHAR_BTREE_OPS_OID && lc_collate_is_c());
2063 case OID_NAME_LIKE_OP:
2064 case OID_NAME_ICLIKE_OP:
2065 case OID_NAME_REGEXEQ_OP:
2066 case OID_NAME_ICREGEXEQ_OP:
2068 (opclass == NAME_PATTERN_BTREE_OPS_OID) ||
2069 (opclass == NAME_BTREE_OPS_OID && lc_collate_is_c());
2072 case OID_BYTEA_LIKE_OP:
2073 isIndexable = (opclass == BYTEA_BTREE_OPS_OID);
2076 case OID_INET_SUB_OP:
2077 case OID_INET_SUBEQ_OP:
2078 isIndexable = (opclass == INET_BTREE_OPS_OID);
2081 case OID_CIDR_SUB_OP:
2082 case OID_CIDR_SUBEQ_OP:
2083 isIndexable = (opclass == CIDR_BTREE_OPS_OID);
2091 * expand_indexqual_conditions
2092 * Given a list of sublists of RestrictInfo nodes, produce a flat list
2093 * of index qual clauses. Standard qual clauses (those in the index's
2094 * opclass) are passed through unchanged. Boolean clauses and "special"
2095 * index operators are expanded into clauses that the indexscan machinery
2096 * will know what to do with.
2098 * The input list is ordered by index key, and so the output list is too.
2099 * (The latter is not depended on by any part of the planner, so far as I can
2100 * tell; but some parts of the executor do assume that the indxqual list
2101 * ultimately delivered to the executor is so ordered. One such place is
2102 * _bt_preprocess_keys() in the btree support. Perhaps that ought to be fixed
2103 * someday --- tgl 7/00)
2106 expand_indexqual_conditions(IndexOptInfo *index, List *clausegroups)
2108 List *resultquals = NIL;
2109 ListCell *clausegroup_item;
2111 Oid *classes = index->classlist;
2113 if (clausegroups == NIL)
2116 clausegroup_item = list_head(clausegroups);
2119 Oid curClass = classes[0];
2122 foreach(l, (List *) lfirst(clausegroup_item))
2124 RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
2126 /* First check for boolean cases */
2127 if (IsBooleanOpclass(curClass))
2131 boolqual = expand_boolean_index_clause((Node *) rinfo->clause,
2136 resultquals = lappend(resultquals,
2137 make_restrictinfo(boolqual,
2143 resultquals = list_concat(resultquals,
2144 expand_indexqual_condition(rinfo,
2148 clausegroup_item = lnext(clausegroup_item);
2152 } while (clausegroup_item != NULL && !DoneMatchingIndexKeys(classes));
2154 Assert(clausegroup_item == NULL); /* else more groups than indexkeys */
2160 * expand_boolean_index_clause
2161 * Convert a clause recognized by match_boolean_index_clause into
2162 * a boolean equality operator clause.
2164 * Returns NULL if the clause isn't a boolean index qual.
2167 expand_boolean_index_clause(Node *clause,
2169 IndexOptInfo *index)
2172 if (match_index_to_operand(clause, indexcol, index))
2174 /* convert to indexkey = TRUE */
2175 return make_opclause(BooleanEqualOperator, BOOLOID, false,
2177 (Expr *) makeBoolConst(true, false));
2180 if (not_clause(clause))
2182 Node *arg = (Node *) get_notclausearg((Expr *) clause);
2184 /* It must have matched the indexkey */
2185 Assert(match_index_to_operand(arg, indexcol, index));
2186 /* convert to indexkey = FALSE */
2187 return make_opclause(BooleanEqualOperator, BOOLOID, false,
2189 (Expr *) makeBoolConst(false, false));
2191 if (clause && IsA(clause, BooleanTest))
2193 BooleanTest *btest = (BooleanTest *) clause;
2194 Node *arg = (Node *) btest->arg;
2196 /* It must have matched the indexkey */
2197 Assert(match_index_to_operand(arg, indexcol, index));
2198 if (btest->booltesttype == IS_TRUE)
2200 /* convert to indexkey = TRUE */
2201 return make_opclause(BooleanEqualOperator, BOOLOID, false,
2203 (Expr *) makeBoolConst(true, false));
2205 if (btest->booltesttype == IS_FALSE)
2207 /* convert to indexkey = FALSE */
2208 return make_opclause(BooleanEqualOperator, BOOLOID, false,
2210 (Expr *) makeBoolConst(false, false));
2220 * expand_indexqual_condition --- expand a single indexqual condition
2221 * (other than a boolean-qual case)
2223 * The input is a single RestrictInfo, the output a list of RestrictInfos
2226 expand_indexqual_condition(RestrictInfo *rinfo, Oid opclass)
2228 Expr *clause = rinfo->clause;
2229 /* we know these will succeed */
2230 Node *leftop = get_leftop(clause);
2231 Node *rightop = get_rightop(clause);
2232 Oid expr_op = ((OpExpr *) clause)->opno;
2233 Const *patt = (Const *) rightop;
2234 Const *prefix = NULL;
2236 Pattern_Prefix_Status pstatus;
2242 * LIKE and regex operators are not members of any index
2243 * opclass, so if we find one in an indexqual list we can
2244 * assume that it was accepted by
2245 * match_special_index_operator().
2247 case OID_TEXT_LIKE_OP:
2248 case OID_BPCHAR_LIKE_OP:
2249 case OID_NAME_LIKE_OP:
2250 case OID_BYTEA_LIKE_OP:
2251 pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like,
2253 result = prefix_quals(leftop, opclass, prefix, pstatus);
2256 case OID_TEXT_ICLIKE_OP:
2257 case OID_BPCHAR_ICLIKE_OP:
2258 case OID_NAME_ICLIKE_OP:
2259 /* the right-hand const is type text for all of these */
2260 pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC,
2262 result = prefix_quals(leftop, opclass, prefix, pstatus);
2265 case OID_TEXT_REGEXEQ_OP:
2266 case OID_BPCHAR_REGEXEQ_OP:
2267 case OID_NAME_REGEXEQ_OP:
2268 /* the right-hand const is type text for all of these */
2269 pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex,
2271 result = prefix_quals(leftop, opclass, prefix, pstatus);
2274 case OID_TEXT_ICREGEXEQ_OP:
2275 case OID_BPCHAR_ICREGEXEQ_OP:
2276 case OID_NAME_ICREGEXEQ_OP:
2277 /* the right-hand const is type text for all of these */
2278 pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
2280 result = prefix_quals(leftop, opclass, prefix, pstatus);
2283 case OID_INET_SUB_OP:
2284 case OID_INET_SUBEQ_OP:
2285 case OID_CIDR_SUB_OP:
2286 case OID_CIDR_SUBEQ_OP:
2287 result = network_prefix_quals(leftop, expr_op, opclass,
2292 result = list_make1(rinfo);
2300 * Given a fixed prefix that all the "leftop" values must have,
2301 * generate suitable indexqual condition(s). opclass is the index
2302 * operator class; we use it to deduce the appropriate comparison
2303 * operators and operand datatypes.
2306 prefix_quals(Node *leftop, Oid opclass,
2307 Const *prefix_const, Pattern_Prefix_Status pstatus)
2315 Assert(pstatus != Pattern_Prefix_None);
2319 case TEXT_BTREE_OPS_OID:
2320 case TEXT_PATTERN_BTREE_OPS_OID:
2324 case VARCHAR_BTREE_OPS_OID:
2325 case VARCHAR_PATTERN_BTREE_OPS_OID:
2326 datatype = VARCHAROID;
2329 case BPCHAR_BTREE_OPS_OID:
2330 case BPCHAR_PATTERN_BTREE_OPS_OID:
2331 datatype = BPCHAROID;
2334 case NAME_BTREE_OPS_OID:
2335 case NAME_PATTERN_BTREE_OPS_OID:
2339 case BYTEA_BTREE_OPS_OID:
2340 datatype = BYTEAOID;
2344 /* shouldn't get here */
2345 elog(ERROR, "unexpected opclass: %u", opclass);
2350 * If necessary, coerce the prefix constant to the right type. The
2351 * given prefix constant is either text or bytea type.
2353 if (prefix_const->consttype != datatype)
2357 switch (prefix_const->consttype)
2360 prefix = DatumGetCString(DirectFunctionCall1(textout,
2361 prefix_const->constvalue));
2364 prefix = DatumGetCString(DirectFunctionCall1(byteaout,
2365 prefix_const->constvalue));
2368 elog(ERROR, "unexpected const type: %u",
2369 prefix_const->consttype);
2372 prefix_const = string_to_const(prefix, datatype);
2377 * If we found an exact-match pattern, generate an "=" indexqual.
2379 if (pstatus == Pattern_Prefix_Exact)
2381 oproid = get_opclass_member(opclass, InvalidOid,
2382 BTEqualStrategyNumber);
2383 if (oproid == InvalidOid)
2384 elog(ERROR, "no = operator for opclass %u", opclass);
2385 expr = make_opclause(oproid, BOOLOID, false,
2386 (Expr *) leftop, (Expr *) prefix_const);
2387 result = list_make1(make_restrictinfo(expr, true, true));
2392 * Otherwise, we have a nonempty required prefix of the values.
2394 * We can always say "x >= prefix".
2396 oproid = get_opclass_member(opclass, InvalidOid,
2397 BTGreaterEqualStrategyNumber);
2398 if (oproid == InvalidOid)
2399 elog(ERROR, "no >= operator for opclass %u", opclass);
2400 expr = make_opclause(oproid, BOOLOID, false,
2401 (Expr *) leftop, (Expr *) prefix_const);
2402 result = list_make1(make_restrictinfo(expr, true, true));
2405 * If we can create a string larger than the prefix, we can say
2409 greaterstr = make_greater_string(prefix_const);
2412 oproid = get_opclass_member(opclass, InvalidOid,
2413 BTLessStrategyNumber);
2414 if (oproid == InvalidOid)
2415 elog(ERROR, "no < operator for opclass %u", opclass);
2416 expr = make_opclause(oproid, BOOLOID, false,
2417 (Expr *) leftop, (Expr *) greaterstr);
2418 result = lappend(result, make_restrictinfo(expr, true, true));
2425 * Given a leftop and a rightop, and a inet-class sup/sub operator,
2426 * generate suitable indexqual condition(s). expr_op is the original
2427 * operator, and opclass is the index opclass.
2430 network_prefix_quals(Node *leftop, Oid expr_op, Oid opclass, Datum rightop)
2443 case OID_INET_SUB_OP:
2447 case OID_INET_SUBEQ_OP:
2451 case OID_CIDR_SUB_OP:
2455 case OID_CIDR_SUBEQ_OP:
2460 elog(ERROR, "unexpected operator: %u", expr_op);
2465 * create clause "key >= network_scan_first( rightop )", or ">" if the
2466 * operator disallows equality.
2470 opr1oid = get_opclass_member(opclass, InvalidOid,
2471 BTGreaterEqualStrategyNumber);
2472 if (opr1oid == InvalidOid)
2473 elog(ERROR, "no >= operator for opclass %u", opclass);
2477 opr1oid = get_opclass_member(opclass, InvalidOid,
2478 BTGreaterStrategyNumber);
2479 if (opr1oid == InvalidOid)
2480 elog(ERROR, "no > operator for opclass %u", opclass);
2483 opr1right = network_scan_first(rightop);
2485 expr = make_opclause(opr1oid, BOOLOID, false,
2487 (Expr *) makeConst(datatype, -1, opr1right,
2489 result = list_make1(make_restrictinfo(expr, true, true));
2491 /* create clause "key <= network_scan_last( rightop )" */
2493 opr2oid = get_opclass_member(opclass, InvalidOid,
2494 BTLessEqualStrategyNumber);
2495 if (opr2oid == InvalidOid)
2496 elog(ERROR, "no <= operator for opclass %u", opclass);
2498 opr2right = network_scan_last(rightop);
2500 expr = make_opclause(opr2oid, BOOLOID, false,
2502 (Expr *) makeConst(datatype, -1, opr2right,
2504 result = lappend(result, make_restrictinfo(expr, true, true));
2510 * Handy subroutines for match_special_index_operator() and friends.
2514 * Generate a Datum of the appropriate type from a C string.
2515 * Note that all of the supported types are pass-by-ref, so the
2516 * returned value should be pfree'd if no longer needed.
2519 string_to_datum(const char *str, Oid datatype)
2522 * We cheat a little by assuming that textin() will do for bpchar and
2523 * varchar constants too...
2525 if (datatype == NAMEOID)
2526 return DirectFunctionCall1(namein, CStringGetDatum(str));
2527 else if (datatype == BYTEAOID)
2528 return DirectFunctionCall1(byteain, CStringGetDatum(str));
2530 return DirectFunctionCall1(textin, CStringGetDatum(str));
2534 * Generate a Const node of the appropriate type from a C string.
2537 string_to_const(const char *str, Oid datatype)
2539 Datum conval = string_to_datum(str, datatype);
2541 return makeConst(datatype, ((datatype == NAMEOID) ? NAMEDATALEN : -1),
2542 conval, false, false);