Skip site navigation (1) Skip section navigation (2)

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

From: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
To: Kevin Barnard <kevin(dot)barnard(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: This query is still running after 10 hours...
Date: 2004-09-29 03:51:56
Message-ID: 20040928215156.65c36a5a@thunder.mshome.net (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-09-29 05:10:47
Subject: Re: Poor Performance for large queries in functions
Previous:From: Robert CreagerDate: 2004-09-29 03:44:24
Subject: Re: This query is still running after 10 hours...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group