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

When grilled further on (Tue, 28 Sep 2004 21:41:50 -0500),
Kevin Barnard <kevin.barnard@gmail.com> confessed:

> On Tue, 28 Sep 2004 20:21:40 -0600, Robert Creager
> <robert_creager@logicalchaos.org> wrote:
> >
> > The trigger keeps another table (catalog) up to date with the information
> > from the obs_v and obs_i tables.  There are no direct insert/update/delete's
> > on the catalog table, only though the trigger.
> >
>
> It's possible that the update to catalog is what is really taking a
> long time.  You might wish to try and explain that query just to make
> sure.  You might also wish to disable to trigger just to rule it out.
> Does catalog have any triggers on it?  Does it have any foreign keys?

A select on the catalog is really quick (54ms on a random query - ~1M entries).  The updates use the index.  The
catalogtable has no triggers or foreign keys.  The trigger on the obs_? tables manages the catalog table. 

tassiv=# \d catalog
                                Table "public.catalog"
      Column      |       Type       |                    Modifiers
------------------+------------------+-------------------------------------------------
 star_id          | integer          | not null default nextval('"star_id_seq"'::text)
 loc_count        | integer          | default 0
 loc              | spoint           | not null
 ra_sum           | double precision | default 0
 ra_sigma         | real             | default 0
 ra_sum_square    | double precision | default 0
 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" gist (loc)


--
 21:44:49 up 6 days, 11 min,  2 users,  load average: 2.03, 2.17, 2.39
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004