From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Kevin Barnard <kevin.barnard@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: This query is still running after 10 hours...
Date: Tue, 28 Sep 2004 21:51:56 -0600
Message-ID: <20040928215156.65c36a5a@thunder.mshome.net> (raw)
In-Reply-To: <b068057c040928194178244b00@mail.gmail.com>
References: <20040928081957.7a317a95@thunder.mshome.net>
<b068057c04092807275edec779@mail.gmail.com>
<b068057c040928072840890326@mail.gmail.com>
<20040928202140.4ea9d090@thunder.mshome.net>
<b068057c040928194178244b00@mail.gmail.com>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, kevin.barnard@gmail.com
Subject: Re: This query is still running after 10 hours...
In-Reply-To: <20040928215156.65c36a5a@thunder.mshome.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox