Re: db growing out of proportion - Mailing list pgsql-bugs

On Sat, 31 May 2003 00:11:26 -0400
Tom Lane <tgl@sss.pgh.pa.us> said something like:
>
> Cool ... but it's not immediately obvious which of these changes did the
> trick for you.  What settings were you at before?  And what's the
> details of the problem query?
>
> The first three settings you mention all seem like reasonable choices,
> but I'd be hesitant to recommend 64M sort_mem for general use (it won't
> take very many concurrent sorts to drive you into the ground...).  So
> I'm interested to narrow down exactly what was the issue here.
>
>             regards, tom lane

shared_buffers was 1024, now 8192
max_fsm_relations was 1000, now 10000
max_fsm_pages was 20000, now 100000
wal_buffers was 8, now 16
sort_mem was 1024, now 64000
vacuum_mem was 1024, now 64000
effective_cache_size was 1000, now 100000

I am in the process of reloading the dB, but obs_v and obs_i contain ~750000 records each.  I'd be happy to play around
withthe settings if you would like to see the timing results.  I'll also be able to get some explain analyze results
tomorrowwhen finished reloading.  Suggestions as to what values to change first? 

There is a 'C' language trigger on the obs_v and obs_i tables which essentially combines the data from the the obs_?
tablesand updates the catalog table when the obs_? records are updated. 

The query is:

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

which is called from a perl script (DBD::Pg - which sets $group_id), and the relevant tables are:

                       Table "public.obs_v"
 Column  |  Type   |                   Modifiers
---------+---------+------------------------------------------------
 x       | real    | not null
 y       | real    | not null
 imag    | real    | not null
 smag    | real    | not null
 ra      | real    | not null
 dec     | real    | not null
 obs_id  | integer | not null default nextval('"obs_id_seq"'::text)
 file_id | integer |
 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 btree (ra, "dec"),
         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 UPDATE NO ACTION ON
DELETECASCADE 
Triggers: obs_v_trig

with obs_i being identical (inherited from same root table)

   Table "public.color_group"
  Column  |  Type   | Modifiers
----------+---------+-----------
 group_id | integer |
 color_u  | real    | default 0
 color_b  | real    | default 0
 color_v  | real    | default 0
 color_r  | real    | default 0
 color_i  | real    | default 0
Indexes: color_group_group_id_index btree (group_id)
Foreign Key constraints: $1 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON UPDATE NO ACTION ON DELETE CASCADE

   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),
         zero_pair_pair_id_index btree (pair_id)
Foreign Key constraints: $1 FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON UPDATE NO ACTION ON DELETE CASCADE

                                    Table "public.files"
  Column  |           Type           |                       Modifiers
----------+--------------------------+-------------------------------------------------------
 file_id  | integer                  | not null default nextval('"files_file_id_seq"'::text)
 group_id | integer                  |
 pair_id  | integer                  |
 date     | timestamp with time zone | not null
 name     | character varying        | not null
 ra_min   | real                     | default 0
 ra_max   | real                     | default 0
 dec_min  | real                     | default 0
 dec_max  | real                     | default 0
Indexes: files_pkey primary key btree (file_id),
         files_name_key unique btree (name),
         files_id_index btree (file_id, group_id, pair_id),
         files_range_index btree (ra_min, ra_max, dec_min, dec_max),
         imported__file_id_idex btree (file_id)
Foreign Key constraints: $1 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON UPDATE NO ACTION ON DELETE CASCADE,
                         $2 FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON UPDATE NO ACTION ON DELETE CASCADE

                                Table "public.catalog"
      Column      |       Type       |                    Modifiers
------------------+------------------+-------------------------------------------------
 star_id          | integer          | not null default nextval('"star_id_seq"'::text)
 loc_count        | integer          | default 0
 ra               | real             | not null
 ra_sum           | double precision | default 0
 ra_sigma         | real             | default 0
 ra_sum_square    | double precision | default 0
 dec              | real             | not null
 dec_sum          | double precision | default 0
 dec_sigma        | real             | default 0
 dec_sum_square   | double precision | default 0
 mag_u_count      | integer          | default 0
 mag_u            | real             | default 99
 mag_u_sum        | double precision | default 0
 mag_u_sigma      | real             | default 0
 mag_u_sum_square | double precision | default 0
 mag_b_count      | integer          | default 0
 mag_b            | real             | default 99
 mag_b_sum        | double precision | default 0
 mag_b_sigma      | real             | default 0
 mag_b_sum_square | double precision | default 0
 mag_v_count      | integer          | default 0
 mag_v            | real             | default 99
 mag_v_sum        | double precision | default 0
 mag_v_sigma      | real             | default 0
 mag_v_sum_square | double precision | default 0
 mag_r_count      | integer          | default 0
 mag_r            | real             | default 99
 mag_r_sum        | double precision | default 0
 mag_r_sigma      | real             | default 0
 mag_r_sum_square | double precision | default 0
 mag_i_count      | integer          | default 0
 mag_i            | real             | default 99
 mag_i_sum        | double precision | default 0
 mag_i_sigma      | real             | default 0
 mag_i_sum_square | double precision | default 0
Indexes: catalog_pkey primary key btree (star_id),
         catalog_ra_decl_index btree (ra, "dec"),
         catalog_star_id_index btree (star_id)



--
O_