Thread

1. Re: Running update in chunks?

   Kevin Grittner <kgrittn@mail.com> — 2013-01-21T22:05:23Z

   Tim Uckun wrote:
   
   > If you have any suggestions I am all ears. For the purposes of this
   > discussion we can narrow down the problem this update statement.
   > 
   > Update imports set make_id = null.
   
   Well, that simplifies things.
   
   First off, what does it say for rows affected? (Hint, if you really
   are using a default configuration and it doesn't say 0 rows
   affected, please show us the actual query used.)
   
   Second, try connecting to the database as a superuser and running:
   
   VACUUM ANALYZE imports;
   -- (show us the results)
   VACUUM FULL imports;
   VACUUM FREEZE ANALYZE;  -- (don't specify a table)
   
   Then try your query and see whether performance is any different.
   
   -Kevin
   
   
   

2. Re: Running update in chunks?

   Tim Uckun <timuckun@gmail.com> — 2013-01-21T23:36:10Z

   > First off, what does it say for rows affected? (Hint, if you really
   > are using a default configuration and it doesn't say 0 rows
   > affected, please show us the actual query used.)
   
   update imports set make_id = null
   
   Query returned successfully: 98834 rows affected, 49673 ms execution time.
   
   
   vacuum analyze imports
   
   Query returned successfully with no result in 4138 ms.
   
   VACUUM FULL imports;
   
   Query returned successfully with no result in 38106 ms.
   
   VACUUM FREEZE ANALYZE;
   
   Query returned successfully with no result in 184635 ms
   
   update imports set make_id = 0
   
   Query returned successfully: 98834 rows affected, 45860 ms execution time.
   
   
   So all the vacuuming saved about four seconds of execution time.
   
   here is the postgresql.conf completely untouched from the default
   install https://gist.github.com/4590590
   
   
   

3. Re: Running update in chunks?

   Jeff Janes <jeff.janes@gmail.com> — 2013-01-22T07:02:57Z

   On Monday, January 21, 2013, Tim Uckun wrote:
   
   > > First off, what does it say for rows affected? (Hint, if you really
   > > are using a default configuration and it doesn't say 0 rows
   > > affected, please show us the actual query used.)
   >
   > update imports set make_id = null
   >
   > Query returned successfully: 98834 rows affected, 49673 ms execution time.
   >
   >
   > vacuum analyze imports
   >
   > Query returned successfully with no result in 4138 ms.
   >
   > VACUUM FULL imports;
   >
   
   What if you do:
   alter table cars.imports set (fillfactor=50);
   Before the vacuum full, and then try the update again?
   
   Cheers,
   
   Jeff
   

4. Re: Running update in chunks?

   Tim Uckun <timuckun@gmail.com> — 2013-01-25T08:57:31Z

   >
   > What if you do:
   > alter table cars.imports set (fillfactor=50);
   > Before the vacuum full, and then try the update again?
   
   
   This makes a dramatic difference when combined with a vacuum.
   
   UPDATE 98834
   Time: 3408.210 ms
   
   Ten times faster!
   
   
   

5. Re: Running update in chunks?

   Richard Huxton <dev@archonet.com> — 2013-01-25T09:23:07Z

   On 25/01/13 08:57, Tim Uckun wrote:
   >> What if you do:
   >> alter table cars.imports set (fillfactor=50);
   >> Before the vacuum full, and then try the update again?
   >
   > This makes a dramatic difference when combined with a vacuum.
   >
   > UPDATE 98834
   > Time: 3408.210 ms
   >
   > Ten times faster!
   That suggests (to me, at least) that it is related to index updating. 
   Again, your GIN index seems primary candidate.
   
   A fillfactor of 50% means row updates probably stay on the same 
   disk-block as their previous version. This implies less index updates.
   
   Try running iostat (I think that's available on a Mac) with/without the 
   fillfactor and with/without the GIN index while you do the updates. It's 
   possible your SSD is just behaving oddly under stress.
   
   --
      Richard Huxton
      Archonet Ltd
   
   
   

6. Re: Running update in chunks?

   Tim Uckun <timuckun@gmail.com> — 2013-01-25T11:38:53Z

   >
   > That suggests (to me, at least) that it is related to index updating. Again,
   > your GIN index seems primary candidate.
   >
   > Try running iostat (I think that's available on a Mac) with/without the
   > fillfactor and with/without the GIN index while you do the updates. It's
   > possible your SSD is just behaving oddly under stress.
   >
   
   
   I dropped the index and the numbers shot up tenfold or more.  I don't
   know why postgres feels the need to update the GIN index on the hstore
   field when I am only updating an integer field but it looks like I
   need to split the hstore into a different table.
   
   
   

7. Re: Running update in chunks?

   Richard Huxton <dev@archonet.com> — 2013-01-25T11:49:36Z

   On 25/01/13 11:38, Tim Uckun wrote:
   >> That suggests (to me, at least) that it is related to index updating. Again,
   >> your GIN index seems primary candidate.
   >>
   >> Try running iostat (I think that's available on a Mac) with/without the
   >> fillfactor and with/without the GIN index while you do the updates. It's
   >> possible your SSD is just behaving oddly under stress.
   >>
   >
   > I dropped the index and the numbers shot up tenfold or more.  I don't
   > know why postgres feels the need to update the GIN index on the hstore
   > field when I am only updating an integer field but it looks like I
   > need to split the hstore into a different table.
   If the row moves to a different block, then it has no choice. The old 
   index entry will point to an invalid block. There are some optimisations 
   (HOT - http://pgsql.tapoueh.org/site/html/misc/hot.html) but that relies 
   on (iirc) the update staying on the same block and also not updating any 
   indexed fields (and you were, I think).
   
   A GIN index is very expensive to update compared to btree too.
   --
      Richard Huxton
      Archonet Ltd
   
   
   

8. Re: Running update in chunks?

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2013-01-25T11:52:26Z

   Tim Uckun wrote:
   > I dropped the index and the numbers shot up tenfold or more.  I don't
   > know why postgres feels the need to update the GIN index on the hstore
   > field when I am only updating an integer field but it looks like I
   > need to split the hstore into a different table.
   
   Every UPDATE that is not HOT will create a row version with
   a new "row id".  That means that all indexes referencing that
   row will have to get updated.
   
   That is consistent with better performance with low fillfactor
   (which makes HOT more likely).
   
   Yours,
   Laurenz Albe
   
   

9. Re: Running update in chunks?

   Jeff Janes <jeff.janes@gmail.com> — 2013-01-25T17:58:54Z

   On Fri, Jan 25, 2013 at 3:38 AM, Tim Uckun <timuckun@gmail.com> wrote:
   >>
   >> That suggests (to me, at least) that it is related to index updating. Again,
   >> your GIN index seems primary candidate.
   >>
   >> Try running iostat (I think that's available on a Mac) with/without the
   >> fillfactor and with/without the GIN index while you do the updates. It's
   >> possible your SSD is just behaving oddly under stress.
   >>
   >
   >
   > I dropped the index and the numbers shot up tenfold or more.  I don't
   > know why postgres feels the need to update the GIN index on the hstore
   > field when I am only updating an integer field
   
   
   When the row gets updated, it might move to some place else.  An index
   maps data values to row locations.  So if the location changes, all
   indexes need to be updated, even if the data value for that index did
   not change.  (Well, I shouldn't say they *need* to change.  The
   database could have been designed, with considerable difficulty and
   consequences, to leave behind permanent redirect pointers to the new
   location.  But it wasn't)
   
   There is a mechanism called HOT update (Heap-Only Tuple) which can
   prevent this under certain conditions.
   
   1) Either none of the fields being updated are indexed, or any that
   are both updated and indexed are updated to the value they already
   have.
   
   2) There is room for a new copy of the tuple on the same page as the old one.
   
   lowering the fillfactor helps with requirement 2, especially since
   your tuples are probably wide (because of the hstore column) and so
   not many fit on a page.
   
   Note that if you update a field to have the same value as it already
   does, it still makes a new copy of the entire tuple anyway.  (It
   detects that the :old = :new for HOT-eligibility purposes if the field
   is indexed, but not for suppression of copying purposes.  And if the
   tuple needs to be copied but there is no room on that page, then it
   isn't eligible for HOT after all).
   
   So you should add a where clause to the UPDATE to filter out things
   that are unchanged.
   
   
   > but it looks like I
   > need to split the hstore into a different table.
   
   That would be one solution, but I think a better one would be to not
   store "make_id" in "imports" in the first place, but instead to always
   fetch it by joining "imports" to "models" at query time.
   
   Cheers,
   
   Jeff
   
   
   

10. Re: Running update in chunks?

    Tim Uckun <timuckun@gmail.com> — 2013-01-27T00:55:05Z

    >
    > That would be one solution, but I think a better one would be to not
    > store "make_id" in "imports" in the first place, but instead to always
    > fetch it by joining "imports" to "models" at query time.
    >
    
    My problem here is that the incoming data is quite messy so the join
    conditions become weird (lots of ORs and such). A multi pass approach
    seems to work better.