-2

I'm updating a table with million records with a simple query but its taking huge timimg, wondering if someone could bring some magic with alternative to speed the process query bellow

UPDATE sources.product
   SET partial=left(full,7);
Jorge Vidinha
  • 404
  • 7
  • 20
  • 2
    Tried to do it in smaller chunks? Too big transactions may slow things down. – jarlh Sep 09 '15 at 14:20
  • there is nothing you can do to speed this up. one way or another, you'll have to update all the rows, and splitting it up into chunks opens the possibility of missing something. – Marc B Sep 09 '15 at 14:23
  • 1
    You're aware that this query will update *every* record in your table, right? – Bob Kaufman Sep 09 '15 at 14:23
  • @BobKaufman , yes i know its a new attribute to be added on every record. – Jorge Vidinha Sep 09 '15 at 14:25
  • Maybe here you can find suitable solution http://stackoverflow.com/questions/3361291/slow-simple-update-query-on-postgresql-database-with-3-million-rows – Darkwing Sep 09 '15 at 14:26
  • 4
    The question is why you're doing this? Do you need to copy column full's data to partial? Can't a view do this? (Data inconsistency safe alternative.) – jarlh Sep 09 '15 at 14:27
  • dont get it , why the negative points , whats the point ? demotivate ? – Jorge Vidinha Sep 09 '15 at 14:27
  • 2
    I am with jarlh here. If you want `partial` always to equal `left(full,7)` then don't store it. You would only introduce redundancy. If, on the other hand, `partial` is subject to Change, then this is just an initial filling done only once, so why care about the time it takes? – Thorsten Kettner Sep 09 '15 at 14:35
  • Ok i think i got your point , but one last doubt . Later of when querying data will it cost the same ? Example querying for left(full,7) will take me the same time as querying an already partial attribute created on the table ? – Jorge Vidinha Sep 09 '15 at 14:44
  • 1
    No, it is usually not as fast, because a string operation must be done rather than just reading the value. But you can create a functional index for quick access: `create index idx_product_leftfull7 on product( left(full,7) );`. So you have it fast without any redundancy in your data. – Thorsten Kettner Sep 09 '15 at 14:53

2 Answers2

1

You need to narrow the number of rows to make it go faster. Try a few things:

  1. Reduce the number of indexes on the partial column. Each index requires an update when you change partial so one update may cause 2 or 3 other updates.

  2. Timestamp your rows so you only update new ones.

  3. Create a trigger to update partial when a row is inserted or updated.

Code Different
  • 90,614
  • 16
  • 144
  • 163
  • the table is stable no new additions will ocurr, just need to add this extra attribute (partial) based on a existent one (full) , also there is no index on attribute partial at this time – Jorge Vidinha Sep 09 '15 at 14:33
  • If this is a one-time job just take the hit and get it done. – Code Different Sep 09 '15 at 14:34
-1

indexing is necessary for a table if it contains big data, i think you should try re-indexing and then try using this command.

Hamza Abdul Jabbar
  • 1
  • 2
  • 1
    Indexes typically make updates slower.... as then it has to update both the underlying table and the index too. Indexes speed queries, not updates, unless it's an index on a reference to the update. – RThomas Sep 09 '15 at 17:45