Results 1 to 4 of 4

Thread: Slow update

  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: Slow update

    Hi, I have a table with over 500K rows, and I want to update some
    "timestamp without time zone" fields.

    UPDATE order_update SET last_attempt_ts = (last_attempt_ts AT TIME ZONE 'EST') AT TIME ZONE 'UTC';

    But this sql is slow, is there a better and faster way to do this ?

  2. #2
    Join Date
    Nov 2013
    Posts
    3

    try to Insert and delete instead of update

    Instead of updating big table , store the data to a temporary table, and

    1.compare temporary table data with original table data,
    2.delete data from original table with temporary table
    3.insert again same data from temporary table to original table.

  3. #3
    Join Date
    Nov 2012
    Posts
    1
    Is there an index on this field? If so, you might try dropping it before running the update, and then recreate it when you're done.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by klind View Post
    Hi, I have a table with over 500K rows, and I want to update some
    "timestamp without time zone" fields.

    UPDATE order_update SET last_attempt_ts = (last_attempt_ts AT TIME ZONE 'EST') AT TIME ZONE 'UTC';
    Well, you are updating all rows in the table, not just "some". This is going to take a while and as you are touching all rows in the table (because no WHERE clause is specified) there is not a good way to speed this up.

    Additionally, before asking performance questions, please read this: https://wiki.postgresql.org/wiki/Slow_Query_Questions
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •