Re: Make UPDATE query quicker?

Hi VB,

1) No idea how long the select is taking unfortunately. How can I
figure this out?
2) I'll stick an index on person_id in the import_table.
3) Not sure what you mean by this unfortunately: "Whats the uniqueness
(the n_distinct from pg_stats table) on all the 3 columns in the WHERE
clause ?"

Thanks

James




On 12 October 2012 15:00, VB N <vbnpgc@gmail.com> wrote:
>
> On Fri, Oct 12, 2012 at 3:46 PM, James David Smith
> <james.david.smith@gmail.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?
>
>
> Any idea how long the SELECT is taking ? Indexes on import_table (an Index
> on person_id) should help. ofcourse it all depends on the uniqueness of the
> column and size of the table etc. Whats the uniqueness (the n_distinct from
> pg_stats table) on all the 3 columns in the WHERE clause ?
>
> Regards,
> VB