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

What does observations_trigger do?



On Tue, 28 Sep 2004 08:19:57 -0600, Robert Creager
<robert_creager@logicalchaos.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
>
>
>
>