This query is still running after 10 hours... - Mailing list pgsql-performance

Help?

Normally, this query takes from 5 minutes to 2 hours to run.  On this update, it's been running for more than 10 hours.

Can it be helped?

UPDATE obs_v
SET mag = obs_v.imag + zp.zero_v + cg.color_v * (obs_v.imag - i.imag),
    use = true
FROM color_groups AS cg, zero_pair AS zp, obs_i AS i, files AS f, groups AS g
WHERE  obs_v.star_id = i.star_id
   AND obs_v.file_id = f.file_id
   AND cg.group_id = g.group_id
   AND g.night_id = f.night_id
   AND g.group_id = $group_id
   AND zp.pair_id = f.pair_id

Hash Join  (cost=130079.22..639663.94 rows=1590204 width=63)
  Hash Cond: ("outer".star_id = "inner".star_id)
  ->  Seq Scan on obs_i i  (cost=0.00..213658.19 rows=10391319 width=8)
  ->  Hash  (cost=129094.19..129094.19 rows=77211 width=59)
        ->  Nested Loop  (cost=250.69..129094.19 rows=77211 width=59)
              ->  Hash Join  (cost=250.69..307.34 rows=67 width=12)
                    Hash Cond: ("outer".pair_id = "inner".pair_id)
                    ->  Seq Scan on zero_pair zp  (cost=0.00..43.32 rows=2532 width=8)
                    ->  Hash  (cost=250.40..250.40 rows=118 width=12)
                          ->  Hash Join  (cost=4.80..250.40 rows=118 width=12)
                                Hash Cond: ("outer".night_id = "inner".night_id)
                                ->  Seq Scan on files f  (cost=0.00..199.28 rows=9028 width=12)
                                ->  Hash  (cost=4.80..4.80 rows=1 width=8)
                                      ->  Nested Loop  (cost=0.00..4.80 rows=1 width=8)
                                            ->  Seq Scan on color_groups cg  (cost=0.00..2.84 rows=1 width=8)
                                                  Filter: (171 = group_id)
                                            ->  Seq Scan on groups g  (cost=0.00..1.95 rows=1 width=8)
                                                  Filter: (group_id = 171)
              ->  Index Scan using obs_v_file_id_index on obs_v  (cost=0.00..1893.23 rows=2317 width=51)
                    Index Cond: (obs_v.file_id = "outer".file_id)

Table definitions:

tassiv=# \d color_groups
                              Table "public.color_groups"
    Column    |  Type   |                           Modifiers
--------------+---------+---------------------------------------------------------------
 group_id     | integer | not null default nextval('"color_groups_group_id_seq"'::text)
 color_u      | real    |
 color_b      | real    |
 color_v      | real    |
 color_r      | real    |
 color_i      | real    |
 max_residual | real    |
Indexes:
    "color_groups_pkey" primary key, btree (group_id)
    "color_group_group_id_index" btree (group_id)

tassiv=# \d zero_pair
   Table "public.zero_pair"
 Column  |  Type   | Modifiers
---------+---------+-----------
 pair_id | integer | not null
 zero_u  | real    | default 0
 zero_b  | real    | default 0
 zero_v  | real    | default 0
 zero_r  | real    | default 0
 zero_i  | real    | default 0
Indexes:
    "zero_pair_pkey" primary key, btree (pair_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON DELETE CASCADE

tassiv=# \d obs_v
                        Table "public.obs_v"
 Column  |  Type   |                   Modifiers
---------+---------+------------------------------------------------
 x       | real    | not null
 y       | real    | not null
 imag    | real    | not null
 smag    | real    | not null
 loc     | spoint  | not null
 obs_id  | integer | not null default nextval('"obs_id_seq"'::text)
 file_id | integer | not null
 use     | boolean | default false
 solve   | boolean | default false
 star_id | integer |
 mag     | real    |
Indexes:
    "obs_v_file_id_index" btree (file_id)
    "obs_v_loc_index" gist (loc)
    "obs_v_obs_id_index" btree (obs_id)
    "obs_v_star_id_index" btree (star_id)
    "obs_v_use_index" btree (use)
Foreign-key constraints:
    "obs_v_files_constraint" FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE
    "obs_v_star_id_constraint" FOREIGN KEY (star_id) REFERENCES catalog(star_id) ON DELETE SET NULL
Triggers:
    obs_v_trig BEFORE INSERT OR DELETE OR UPDATE ON obs_v FOR EACH ROW EXECUTE PROCEDURE observations_trigger
()

tassiv=# \d files
                                      Table "public.files"
  Column  |            Type             |                       Modifiers
----------+-----------------------------+-------------------------------------------------------
 file_id  | integer                     | not null default nextval('"files_file_id_seq"'::text)
 night_id | integer                     |
 pair_id  | integer                     |
 name     | character varying           | not null
 date     | timestamp without time zone |
Indexes:
    "files_pkey" primary key, btree (file_id)
    "files_name_key" unique, btree (name)
    "files_id_index" btree (file_id, night_id, pair_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (night_id) REFERENCES nights(night_id) ON UPDATE CASCADE ON DELETE CASCADE
    "$2" FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON DELETE CASCADE

tassiv=# \d groups
     Table "public.groups"
  Column  |  Type   | Modifiers
----------+---------+-----------
 group_id | integer | not null
 night_id | integer | not null
Indexes:
    "groups_pkey" primary key, btree (group_id, night_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (group_id) REFERENCES color_groups(group_id) ON DELETE CASCADE
    "$2" FOREIGN KEY (night_id) REFERENCES nights(night_id) ON DELETE CASCADE

Server is a dual AMD2600+ with 2Gb mem:

shared_buffers = 20000          # min 16, at least max_connections*2, 8KB each
sort_mem = 16000                # min 64, size in KB
max_fsm_pages = 100000          # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 5000        # min 100, ~50 bytes each
effective_cache_size = 100000   # typically 8KB each
random_page_cost = 2            # units are one sequential page
default_statistics_target = 500 # range 1-1000

Thanks,
Rob

--
 08:06:34 up 5 days, 10:33,  2 users,  load average: 3.13, 3.29, 3.61
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004