From 29f45e299e7ffa1df0db44b8452228625479487f Mon Sep 17 00:00:00 2001 From: David Rowley Date: Wed, 7 Jul 2021 16:29:17 +1200 Subject: Use a hash table to speed up NOT IN(values) Similar to 50e17ad28, which allowed hash tables to be used for IN clauses with a set of constants, here we add the same feature for NOT IN clauses. NOT IN evaluates the same as: WHERE a <> v1 AND a <> v2 AND a <> v3. Obviously, if we're using a hash table we must be exactly equivalent to that and return the same result taking into account that either side of the condition could contain a NULL. This requires a little bit of special handling to make work with the hash table version. When processing NOT IN, the ScalarArrayOpExpr's operator will be the <> operator. To be able to build and lookup a hash table we must use the <>'s negator operator. The planner checks if that exists and is hashable and sets the relevant fields in ScalarArrayOpExpr to instruct the executor to use hashing. Author: David Rowley, James Coleman Reviewed-by: James Coleman, Zhihong Yu Discussion: https://postgr.es/m/CAApHDvoF1mum_FRk6D621edcB6KSHBi2+GAgWmioj5AhOu2vwQ@mail.gmail.com --- src/backend/optimizer/plan/setrefs.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src/backend/optimizer/plan/setrefs.c') diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 61ccfd300b3..210c4b3b14c 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -1687,6 +1687,9 @@ fix_expr_common(PlannerInfo *root, Node *node) if (!OidIsValid(saop->hashfuncid)) record_plan_function_dependency(root, saop->hashfuncid); + + if (!OidIsValid(saop->negfuncid)) + record_plan_function_dependency(root, saop->negfuncid); } else if (IsA(node, Const)) { -- cgit v1.2.3