update不使用索引_大数据知识库

我有一个主键为两个数字列的表。在事务中，我选择第一个（按主键列排序）n行（对于n=500左右）进行更新，处理它们，然后更新它们。

SELECT ...
ORDER BY pk1, pk2
LIMIT 500
FOR UPDATE

现在，我不确定在更新的行中选择这些行的最佳方法是什么 WHERE 条款。我试过这个：

array[pk1, pk2] >= array[$first_pk1_value, $first_pk2_value]
AND array[pk1, pk2] <= array[$last_pk1_value, $last_pk2_value]

（其中 pk1 以及 pk2 是主键列和 ${first,last}_pk{1,2}_value 是在“选择”窗口中扫描的第一行和最后一行的相应列的扫描值
假设数组是按字典顺序排列的，就像 ORDER BY pk1, pk2 是的，这会找到正确的行。
我也试过类似的方法：

(pk1 = $first_pk1_value AND pk2 >= $first_pk2_value)
OR (pk1 > $first_pk1_value AND pk1 < $last_pk1_value)
OR (pk1 = $last_pk1_value AND pk2 <= $last_pk2_value)

两种方法都有效，但都是顺序扫描。因为where子句只是表示主键上的一个范围，所以我希望postgres进行索引扫描。
是postgres不支持在多列索引上选择一个范围，还是我做错了什么？

更新
作为对toni评论的回应，我试着像在python中一样使用tuple，它比我最初的建议效果要好得多。基于 analyze 输出，隐式 row(id1, id2) 与支持pk的索引兼容。

select version();
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

explain analyze 
 select * from testidx_array 
  where (id1, id2) between (8, 150) and (9, 2000);

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on testidx_array  (cost=423.81..1263.91 rows=19855 width=40) (actual time=1.772..4.148 rows=11851 loops=1)
   Recheck Cond: ((ROW(id1, id2) >= ROW(8, 150)) AND (ROW(id1, id2) <= ROW(9, 2000)))
   Heap Blocks: exact=54
   ->  Bitmap Index Scan on testidx_array_pkey  (cost=0.00..418.84 rows=19855 width=0) (actual time=1.722..1.722 rows=11851 loops=1)
         Index Cond: ((ROW(id1, id2) >= ROW(8, 150)) AND (ROW(id1, id2) <= ROW(9, 2000)))
 Planning time: 0.096 ms
 Execution time: 4.867 ms
(7 rows)

下面的旧答案已被取代
您应该能够通过指定索引的范围来强制使用索引 pk1 然后包括一个 and 对于 array[pk1, pk2] 条件。

where pk1 between $first_pk1_value and $last_pk1_value
  and array[pk1, pk2] between array[$first_pk1_value, $first_pk2_value] 
                          and array[$last_pk1_value, $last_pk2_value]

这对我来说在一个测试表中起了作用：

\d testidx_array
            Table "public.testidx_array"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 id1      | integer |           | not null | 
 id2      | integer |           | not null | 
 somedata | text    |           |          | 
Indexes:
    "testidx_array_pkey" PRIMARY KEY, btree (id1, id2)

explain analyze
 select * from testidx_array 
  where array[id1, id2] between array[8,150] and array[9,2000];

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on testidx_array  (cost=0.00..1943.00 rows=500 width=40) (actual time=42.011..50.758 rows=11851 loops=1)
   Filter: ((ARRAY[id1, id2] >= '{8,150}'::integer[]) AND (ARRAY[id1, id2] <= '{9,2000}'::integer[]))
   Rows Removed by Filter: 88149
 Planning time: 0.151 ms
 Execution time: 51.325 ms
(5 rows)

explain analyze 
 select * from testidx_array 
  where id1 between 8 and 9 
    and array[id1, id2] between array[8,150] and array[9,2000];

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on testidx_array  (cost=418.85..1258.91 rows=99 width=40) (actual time=2.278..11.109 rows=11851 loops=1)
   Recheck Cond: ((id1 >= 8) AND (id1 <= 9))
   Filter: ((ARRAY[id1, id2] >= '{8,150}'::integer[]) AND (ARRAY[id1, id2] <= '{9,2000}'::integer[]))
   Rows Removed by Filter: 8149
   Heap Blocks: exact=90
   ->  Bitmap Index Scan on testidx_array_pkey  (cost=0.00..418.82 rows=19853 width=0) (actual time=2.138..2.138 rows=20000 loops=1)
         Index Cond: ((id1 >= 8) AND (id1 <= 9))
 Planning time: 0.289 ms
 Execution time: 11.693 ms
(9 rows)

update不使用索引

1条答案

相关问题

热门标签

最新问答