pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (10+ messages)  latest in thread

Message-ID: <20040928215156.65c36a5a@thunder.mshome.net>
Permalink:  ../20040928215156.65c36a5a@thunder.mshome.net/
Also on:    postgresql.org/message-id/20040928215156.65c36a5a@thunder.mshome.net

 · 

reply

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