Skip site navigation (1) Skip section navigation (2)

Make UPDATE query quicker?

From: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Make UPDATE query quicker?
Date: 2012-10-12 10:16:00
Message-ID: CAMu32ACu5miM4Fc8wx_0ZDs5_PT=j6+PyJ8vQdAjoUbhmtH5og@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Thanks

James


Responses

pgsql-novice by date

Next:From: Michael WoodDate: 2012-10-12 11:53:55
Subject: Re: Make UPDATE query quicker?
Previous:From: Josh KupershmidtDate: 2012-10-10 21:20:26
Subject: Re: check if type is valid pg type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group