From: | James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> |
---|---|
To: | Michael Wood <esiotrot(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Make UPDATE query quicker? |
Date: | 2012-10-12 12:14:17 |
Message-ID: | CAMu32ABDyRc=-wJzmTQqBUMDific14=cMAiBoyzMJrNCFDhn3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Michael,
Thanks, I will give that a try later today.
The first of the 20 updates I need to do took about 25 minutes in the
end by the way.
All other useful suggestions gratefully recevied... ;-)
Thanks
James
On 12 October 2012 12:53, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
> On 12 October 2012 12:16, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
>>
>> Hi all,
>>
>> Wondered if someone had any tips about how to do this UPDATE query
>> quicker. I've got two tables:
>>
>> CREATE TABLE import_table
>> ( date_time TIMESTAMP
>> person_id, INTEGER
>> data REAL)
>>
>> CREATE TABLE master_table
>> (date_time TIMESTAMP
>> person_id INTEGER
>> data REAL)
>>
>> Each table has 172800 rows in it.
>>
>> I want to move the 'data' from the import_table to the master_table by
>> matching on both the date_time and the person_id. I do this with this
>> query:
>>
>> UPDATE master_table a
>> SET data =
>> (SELECT b.date
>> FROM import_table b
>> WHERE a.date_time = b.date_time AND a.person_id = b.person_id AND
>> b.data IS NOT NULL)
>>
>> However I need to do this about 20 times, and the first time is still
>> running at the moment (about 15 minutes).
>>
>> Am I doing something wrong? Should I put some indexes on the columns
>> somehow to improve performance?
>
> I don't know if this will help performance-wise, but maybe it's worth a try:
>
> See if doing it with an UPDATE FROM helps at all. e.g.:
>
> http://stackoverflow.com/a/2766766/495319
>
> --
> Michael Wood <esiotrot(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Dominik Moritz | 2012-10-12 13:45:21 | Re: check if type is valid pg type |
Previous Message | Michael Wood | 2012-10-12 11:53:55 | Re: Make UPDATE query quicker? |