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
pgpfnkAxb9jnT.pgp
Description: PGP signature
