digoal
2021-07-07
PostgreSQL , not in , hash table
再大的not in values都不怕了, 有了hash table. 解决了not in values的list search性能问题.
Use a hash table to speed up NOT IN(values)
master github/master
author David Rowley <drowley@postgresql.org>
Wed, 7 Jul 2021 04:29:17 +0000 (16:29 +1200)
committer David Rowley <drowley@postgresql.org>
Wed, 7 Jul 2021 04:29:17 +0000 (16:29 +1200)
commit 29f45e299e7ffa1df0db44b8452228625479487f
tree 948f286a1db23d164aeb20d4cb3d172ed986e758 tree
parent d854720df6df68cfe1432342e33c9e3020572a51 commit | diff
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
