PostgreSQL中用于WHERE IN句子的多行和多列绑定

Question

PostgreSQL中用于WHERE IN句子的多行和多列绑定

4

因此，我想准备一个类似于以下查询:

SELECT id FROM users WHERE (branch, cid) IN $1;

然后，将一个长度可变的行集绑定到它上面，例如(('a','b'),('c','d'))。

换句话说，就像这样：

pg_prepare($users, 'users_query', 'SELECT id FROM users WHERE (branch, cid) IN $1');
$result = pg_execute($users, 'users_query', array("(('a','b'),('c','d'))");

我需要将它们分开的原因是为了只需准备一次，然后尽可能少地进行多次运行。

- Max

@dbenhur 它确实可以工作。我正在使用PostgreSQL 9.1.3，如果您想要，我可以粘贴输出吗？ - Max

是的，主键是（branch，cid）。这是解释：用户上的位图堆扫描（成本= 8.52..12.54行= 1宽度= 8）重新检查条件：（（（分支= 'a' :: text）AND（cid = 'b' :: text））OR（（分支= 'c' :: text）AND（cid = 'd' :: text））） -> BitmapOr（成本= 8.52..8.52行= 1宽度= 0） ->用户_pkey上的位图索引扫描（成本= 0.00..4.26行= 1宽度= 0）索引条件：（（分支= 'a' :: text）AND（cid = 'b' :: text）） ->用户_pkey上的位图索引扫描（成本= 0.00..4.26行= 1宽度= 0）索引条件：（（分支= 'c' :: text）AND（cid = 'd' :: text））（7行） - Max

我有两行数据，所以没有太多分析的用处 :P 问题是我无法弄清楚如何准备查询并通过绑定提供带括号的子查询。我开始尝试使用ANY()和行构造器，但这导致了顺序扫描，而不是上面的位图堆扫描。 - Max

2

@Alec：如果你只有两行数据，查询优化器很可能会总是选择顺序扫描。花一分钟思考一下。然后再添加更多的行。 - Mike Sherrill 'Cat Recall'

@Catcall 好的，是的，说实话我没有意识到查询优化器会考虑行数。但这并不能回答我如何将值作为参数传递——比如准备一个带有 $1 的查询并稍后绑定值。用 ANY 进行尝试时，我成功地走到了 WHERE branch = ANY($1)，使用{a,c}，但随后我无法通过绑定来传递多个列。当我尝试 EXPLAIN SELECT id FROM users WHERE (branch, cid) = ANY(ARRAY[('a'::text,'b'::text),('c'::text,'d'::text)]); 并且它做了一次顺序扫描时，我放弃了 ANY。 - Max

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dbenhur · Accepted Answer

仅有两条记录时获得顺序扫描是毫无意义的。对于如此小的数据集，索引永远不会比顺序扫描更快。我建立了一个类似于你的小样本表，并填充了一百万行数据，以下查询风格始终产生良好的计划和快速执行：

prepare s4 as
select id from users
join (select * from (values ($1,$2),($3,$4)) as v(branch, cid)) as p
using (branch, cid);

explain analyze execute s4('b11','c11','b1234','c1234');
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.65 rows=1 width=4) (actual time=0.199..0.234 rows=2 loops=1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=64) (actual time=0.002..0.003 rows=2 loops=1)
   ->  Index Scan using u_i on users  (cost=0.00..8.30 rows=1 width=16) (actual time=0.111..0.112 rows=1 loops=2)
         Index Cond: ((users.branch = "*VALUES*".column1) AND (users.cid = "*VALUES*".column2))
 Total runtime: 0.425 ms

看起来你真正的问题是如何将动态确定的值对绑定到你的sql。我的PHP非常生疏，阅读在线文档让我想起了我有多么厌恶它，但我认为以下代码将会做到你想要的，构建上述形式的sql，并根据你希望绑定的值的数量动态创建值对占位符的数量。我手头没有php执行环境，所以我甚至没有检查它是否在语法上正确，但你应该能够理解这个想法并解决我示例中的任何微不足道的错误。

$values = array(
  'a', 'b',
  'c', 'd',
  // etc...
);

$value_placeholders = "";
$sep = "";
for ($i=1; $i <= $count($values); $i+=2) {
  $value_placeholders = $value_placeholders . sprintf("($%u,$%u),", $i, $i+1) . $sep
  $sep = ",";
}

$sql =
  'select id from users ' .
  'join (select * from (values ' . $value_placeholders . ') as v(branch, cid)) as p' .
  'using (branch, cid)';

$result = pg_query_params($dbconn, $sql, $values);

如果你真的只需要一个准备好的语句（对于一个懒得在真实数据集上尝试查询而只有两条记录的人，我们将完全避免谈论过早优化），我想我有一个答案：

create index u_i2 on users ((branch||cid));
prepare sa as select id from users where branch||cid in (select unnest($1::text[]));
explain analyze execute sa(ARRAY['b1c1','b1234c1234']);
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=12.17..645.78 rows=50000 width=4) (actual time=0.169..0.188 rows=2 loops=1)
   ->  HashAggregate  (cost=0.02..0.03 rows=1 width=32) (actual time=0.018..0.019 rows=2 loops=1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.011 rows=2 loops=1)
   ->  Bitmap Heap Scan on users  (cost=12.14..638.25 rows=500 width=16) (actual time=0.082..0.082 rows=1 loops=2)
         Recheck Cond: ((users.branch || users.cid) = (unnest($1)))
         ->  Bitmap Index Scan on u_i2  (cost=0.00..12.02 rows=500 width=0) (actual time=0.078..0.078 rows=1 loops=2)
               Index Cond: ((users.branch || users.cid) = (unnest($1)))
 Total runtime: 0.275 ms

注意：我找不到一种方法来获取行对的索引访问。但是，如果您在这两个字段的串联上创建一个功能性索引，然后提供这些串联的边界数组，您将获得一个良好的快速嵌套循环索引扫描。