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

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

> On Tue, 28 Sep 2004 20:21:40 -0600, Robert Creager
> <robert_creager(at)logicalchaos(dot)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 catalog table 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