使用PostgreSQL中的游标更新性能 [英] Performance on updates using cursor in PostgreSQL

问题描述

我想知道使用以下方法更新表的性能差异：

 更新电影SET kind =戏剧性'c_films的当前电流; 
  

或这样：

  UPDATE films SET kind ='Dramatic'WHERE unique_indexed_int_column = 3000;有没有人测试过这个或知道如何使用游标的更新工作，所以他们可以评论这个？
 
 > 
 
 
编辑：我现在已经做了基准测试，发现它是大约三分之一更快做最新的例子。我运行每个查询100000次并计时差异。我使用psycopg2使用服务器端游标与Postgres通信。我会进一步调查，看看是否可以发现这并不总是这样。
解决方案
我不熟悉PostgreSQL ，所以我只能给你一个通用的答案。
 
 
 
首先，如果 indexed_int_column 不是唯一的，语句将更新多个行，而第一个将仅更新当前在光标 c_films 下的行。所以语句不一样。 
 
 
 
假设唯一性和光标c_films在 indexed_int_column = 3000 的一行，那么更新应该非常快一旦光标位于某一行下，因为光标保存信息以直接访问此行的物理位置。然而，第二个语句必须首先获取索引，查找其中的值3000，然后才知道要更新的行的物理位置。也就是说，这个查找操作必须为光标在一点处完成（如果我们没有遍历整个表）。所以一般来说，只有在你必须先读取数据，然后想更新刚才读取的同一行时，才能使用游标。
 
I would like to know the performance difference in updating a table using the following method:
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
or like this:
UPDATE films SET kind = 'Dramatic' WHERE unique_indexed_int_column = 3000;
Has anyone tested this or know how updates using cursors work so they can comment on this?

EDIT: I have now benchmarked this and found that it is infact around a third faster to do the latest example. I ran each of the queries 100000 times and timed the difference. I used psycopg2 using server side cursors to communicate with Postgres. I will investigate further to see whether I can find that this is not always the case.
解决方案
I'm not familiar with PostgreSQL, so I can only give you a generic answer.

First off, if indexed_int_column is not unique, the second statement will update multiple rows, whereas the first one will only update the row currently under the cursor c_films. So the statements are not identical. 

Assuming uniqueness and the cursor c_films being at the one row where indexed_int_column = 3000, then updating should be very quick once the cursor is positioned under a certain row, as the cursor holds the information to directly access the physical location of this row. The second statement however, will have to fetch the index first, lookup the value 3000 in it and only then it will know the physical location of the row to update. That said, this lookup  operation had to be done for the cursor at one point to (if we did not iterate over the whole table that is). So in general it only pays off to use the cursor, when you have to read the data first anyway, and then want to update the same row you just read.

                        
这篇关于使用PostgreSQL中的游标更新性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！