Re: This query is still running after 10 hours...

What does observations_trigger do?

On Tue, 28 Sep 2004 08:19:57 -0600, Robert Creager
<robert_creager(at)logicalchaos(dot)org> wrote:
>
> 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
>
>
>
>