使用临时表替换 WHERE IN 子句 [英] Using a temporary table to replace a WHERE IN clause
问题描述
我让用户输入了我需要在表中查询的值列表.该列表可能非常大，并且在编译时不知道长度.而不是使用 WHERE ... IN (...) 我认为使用临时表并对其执行连接会更有效.我在另一个 SO 问题中阅读了此建议(目前找不到，但会在我找到时进行编辑).
I've got the user entering a list of values that I need to query for in a table. The list could be potentially very large, and the length isn't known at compile time. Rather than using WHERE ... IN (...) I was thinking it would be more efficient to use a temporary table and execute a join against it. I read this suggestion in another SO question (can't find it at the moment, but will edit when I do).
要点是这样的:
CREATE TEMP TABLE my_temp_table (name varchar(160) NOT NULL PRIMARY KEY);
INSERT INTO my_temp_table VALUES ('hello');
INSERT INTO my_temp_table VALUES ('world');
//... etc
SELECT f.* FROM foo f INNER JOIN my_temp_table t ON f.name = t.name;
DROP TABLE my_temp_table;
如果我有两个同时运行，如果线程 2 尝试在线程 1 之后创建 TEMP 表，我会不会收到错误消息?
If I have two of these going at the same time, would I not get an error if Thread 2 tries to create the TEMP table after Thread 1?
我应该为 TEMP 表随机生成一个名称吗?
Should I randomly generate a name for the TEMP table instead?
或者，如果我将整个事物包装在一个事务中，命名冲突会消失吗?
Or, if I wrap the whole thing in a transaction, will the naming conflict go away?
这是 Postgresql 8.2.
This is Postgresql 8.2.
谢谢！
推荐答案
无需担心冲突.
pg_temp 架构是特定于会话的.如果您在单独的会话中有一个并发语句，它将使用不同的架构(即使您认为它具有相同的名称).
The pg_temp schema is session specific. If you've a concurrent statement in a separate session, it'll use a different schema (even if you see it as having the same name).
但是有两个注意事项:
每次创建临时对象时，系统目录都会创建一个临时模式和对象本身.如果经常使用，这可能会导致混乱.
Every time you create temporary objects, the system catalog creates a temporary schema and the objects themselves. This can lead to clutter if used frequently.
因此，对于小型集合/频繁使用，通常最好坚持使用 in 或 with 语句(Postgres 可以很好地处理这两种语句).通过使用不可变集合返回函数欺骗"计划器使用您正在寻找的任何计划，这有时也很有用.
Thus, for small sets/frequent uses, it's usually better stick to an in or a with statement (both of which Postgres copes quite well with). It's also occasionally useful to "trick" the planner into using whichever plan you're seeking by using an immutable set returning function.
如果您决定实际使用临时表，通常最好在填满临时表后对其进行索引和分析.否则，您所做的只是编写 with 语句.
In the event you decide to actually use temporary tables, it's usually better to index and analyze them once you've filled them up. Else you're doing little more than writing a with statement.
这篇关于使用临时表替换 WHERE IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
