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