postgresql 更新查询中的性能问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24647503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance issue in update query
提问by vara
I have one small doubt in query performance. Basically, I have a table with more than 1C records. sl_idis the primary key in that table. Currently, I am updating the table column statusto true(default false) by using the sl_id.
我对查询性能有一个小疑问。基本上，我有一个超过 1C 记录的表。sl_id是该表中的主键。目前，我正在更新表列status到true（默认false使用）sl_id。
In my program, I will have 200 unique sl_idin an array. I am updating the statusto true(always) by using each sl_id.
在我的程序中，我将有 200 个唯一sl_id的数组。我正在更新status，以true利用每个（总是）sl_id。
My doubt:
我的疑问：
Shall I use individual update queries by specifing each sl_idin a where condition to update the status?
我是否应该通过sl_id在 where 条件中指定每个更新查询来使用单独的更新查询来更新状态？
(OR)
（或者）
Shall I use INoperator and put all 200 unique sl_idin one single query?
我应该使用IN运算符并将所有 200 个唯一sl_id查询放在一个查询中吗？
Which one will be faster?
哪个会更快？
回答by Craig Ringer
In rough order of slower to faster:
按从慢到快的粗略顺序：
- 200 Individual queries, each in their own transaction
- 200 Individual queries, all in one transaction
- 1 big query with
WHERE ... IN (...)orWHERE EXISTS (SELECT ...) - 1 big query with an
INNER JOINover aVALUESclause - (only faster for very big lists of values):
COPYvalue list to a temp table, index it, andJOINon the temp table.
- 200 个单独的查询，每个查询都在自己的事务中
- 200 个单独的查询，全部在一笔交易中
- 1 个大查询
WHERE ... IN (...)或WHERE EXISTS (SELECT ...) - 1 个带有
INNER JOINover aVALUES子句的大查询 - （仅对于非常大的值列表更快）：
COPY值列表到临时表，索引它，并JOIN在临时表上。
If you're using hundreds of values I really suggest joining over a VALUESclause. For many thousands of values, COPYto a temp table and index it then join on it.
如果您使用数百个值，我真的建议您加入一个VALUES子句。对于数以千计的值，COPY到一个临时表并索引它然后加入它。
An example of joining on a values clause. Given this INquery:
加入值子句的示例。鉴于此IN查询：
SELECT *
FROM mytable
WHERE somevalue IN (1, 2, 3, 4, 5);
the equivalent with VALUESis:
相当于VALUES：
SELECT *
FROM mytable
INNER JOIN (
VALUES (1), (2), (3), (4), (5)
) vals(v)
ON (somevalue = v);
Note, however, that using VALUESthis way is a PostgreSQL extension, wheras IN, or using a temporary table, is SQL standard.
但是请注意，使用VALUES这种方式是 PostgreSQL 扩展，而IN使用临时表是 SQL 标准。
See this related question:
看到这个相关的问题：
回答by Kasyx
Definitely you should use WHERE INoperator. Making 200 queries is much slower than one bigger. Remember, when you sending query to database, there is additional time needed to communicate between server and DB and this will crush your performance.
绝对你应该使用WHERE IN运算符。进行 200 次查询比更大的查询慢得多。请记住，当您向数据库发送查询时，服务器和数据库之间需要额外的时间进行通信，这会影响您的性能。
回答by H. Mahida
Definitely IN is more powerful, but again the number of match to check in IN will make performance issue.
肯定 IN 更强大，但再次检查 IN 的匹配数量会导致性能问题。
So, I will suggest to use IN but with BATCH, as in if you have 200 record to update then part in 50 each and then make 4 UPDATE query, or something like that.
因此，我建议使用 IN 但使用 BATCH，就像你有 200 条记录要更新，然后每条记录 50 条，然后进行 4 次 UPDATE 查询，或类似的东西。
Hope it helps...!!
希望能帮助到你...！！
