Re: Make UPDATE query quicker?

On Fri, Oct 12, 2012 at 3:46 PM, 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?
>

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