Performance on updates using cursor in PostgreSQL

Question

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.

Janick Bernet · Accepted Answer · 2011-02-16 08:52:28Z

1

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.

edited Feb 16, 2011 at 8:52

answered Jan 21, 2011 at 22:04

Janick Bernet

21.2k2 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Please see the surprising benchmark between the two on: stackoverflow.com/questions/4776127/…

Janick Bernet Over a year ago

It's not surprising at all, but maybe I did not formulate my answer well. The only case when update by cursor makes sens is when you want to read the date you update first. In any other scenario, obviously reading and then updating is at most as fast, normally slower (especially since there is additional communication roundtrip between the db and the program) than updating directly. (If it wasn't, the DB would be stupid not always read and write afterwards in such case to get the performance...)

Collectives™ on Stack Overflow

Performance on updates using cursor in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related