pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bendik Rognlien Johansen <bendik.johansen@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Problem with query, server totally unresponsive
Date: Thu, 23 Mar 2006 13:12:08 +0100
Message-ID: <0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com> (raw)

Hello, I have a big problem with one of my databases. When i run my  
query, after a few minutes, the postmaster shows 99% mem i top, and  
the server becomes totally unresponsive.

I get this message when I try to cancel the query:

server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


This works fine on a different machine with the same database  
settings and about 30% less records. The other machine is running  
PostgreSQL 8.0.3
The troubled one is running 8.1.2


Any help is greatly appreciated!

Thanks





The machine has 2x Intel dual core processors (3GHz) and 2 Gigs of ram.

#----------------------------------------------------------------------- 
----
# RESOURCE USAGE (except WAL)
#----------------------------------------------------------------------- 
----

# - Memory -

shared_buffers = 8192                   # min 16 or  
max_connections*2, 8KB each
#temp_buffers = 1000                    # min 100, 8KB each
#max_prepared_transactions = 5          # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of  
shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 4096                 # min 64, size in KB
maintenance_work_mem = 262144           # min 1024, size in KB
#max_stack_depth = 2048                 # min 100, size in KB





My query:

SELECT r.id AS id, max(r.name) AS name, max(companyid) AS companyid,  
max(extract(epoch from r.updated)) as r_updated, hydra.join(co.value)  
AS contacts, hydra.join(ad.postalsite) AS postalsites FROM records r  
LEFT OUTER JOIN contacts co ON(r.id = co.record AND co.type IN 
(1,11,101,3)) LEFT OUTER JOIN addresses ad ON(r.id = ad.record) WHERE  
r.original IS NULL GROUP BY r.id;


The hydra.join function
-- Aggregates a column to an array

DROP FUNCTION hydra.join_aggregate(text, text) CASCADE;
DROP FUNCTION hydra.join_aggregate_to_array(text);

CREATE FUNCTION hydra.join_aggregate(text, text) RETURNS text
   AS 'select $1 || ''|'' || $2'
   LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION hydra.join_aggregate_to_array(text) RETURNS text[]
   AS 'SELECT string_to_array($1, ''|'')'
   LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE hydra.join (
   BASETYPE = text
,SFUNC = hydra.join_aggregate
,STYPE = text
,FINALFUNC = hydra.join_aggregate_to_array
);





Tables:
records: 757278 rows
contacts: 2256253 rows
addresses: 741536 rows








Explain:

                                              QUERY PLAN
------------------------------------------------------------------------ 
-----------------------------
GroupAggregate  (cost=636575.63..738618.40 rows=757278 width=75)
    ->  Merge Left Join  (cost=636575.63..694469.65 rows=1681120  
width=75)
          Merge Cond: ("outer".id = "inner".record)
          ->  Merge Left Join  (cost=523248.93..552247.54  
rows=1681120 width=63)
                Merge Cond: ("outer".id = "inner".record)
                ->  Sort  (cost=164044.73..165937.93 rows=757278  
width=48)
                      Sort Key: r.id
                      ->  Seq Scan on records r  (cost=0.00..19134.78  
rows=757278 width=48)
                            Filter: (original IS NULL)
                ->  Sort  (cost=359204.20..363407.00 rows=1681120  
width=19)
                      Sort Key: co.record
                      ->  Seq Scan on contacts co   
(cost=0.00..73438.06 rows=1681120 width=19)
                            Filter: (("type" = 1) OR ("type" = 11) OR  
("type" = 101) OR ("type" = 3))
          ->  Sort  (cost=113326.70..115180.54 rows=741536 width=16)
                Sort Key: ad.record
                ->  Seq Scan on addresses ad  (cost=0.00..20801.36  
rows=741536 width=16)
(16 rows)







se_companies=# \d records;
                                       Table "public.records"
      Column      |           Type           |                       
Modifiers
-----------------+-------------------------- 
+------------------------------------------------------
id              | integer                  | not null default nextval 
('records_id_seq'::regclass)
companyid       | character varying(16)    | default ''::character  
varying
categories      | integer[]                |
nace            | integer[]                |
name            | character varying(255)   | default ''::character  
varying
updated         | timestamp with time zone | default  
('now'::text)::timestamp(6) with time zone
updater         | integer                  |
owner           | integer                  |
loaner          | integer                  |
info            | text                     |
original        | integer                  |
active          | boolean                  | default true
categoryquality | integer                  | not null default 0
searchwords     | character varying(128)[] |
priority        | integer                  |
categorized     | timestamp with time zone |
infopage        | boolean                  |
national        | boolean                  |
password        | character varying(32)    |
login           | boolean                  |
deleted         | boolean                  | not null default false
reference       | integer[]                |
nuinfo          | text                     |
brands          | integer[]                |
volatile        | boolean                  | not null default false
Indexes:
     "records_pkey" PRIMARY KEY, btree (id) CLUSTER
     "original_is_null" btree (original) WHERE original IS NULL
     "records_category_rdtree_idx" gist (categories)
     "records_categoryquality_idx" btree (categoryquality)
     "records_lower_name_idx" btree (lower(name::text))
     "records_original_idx" btree (original)
     "records_owner" btree ("owner")
     "records_updated_idx" btree (updated)
Foreign-key constraints:
     "records_original_fkey" FOREIGN KEY (original) REFERENCES records 
(id)

se_companies=# \d contacts;
                                    Table "public.contacts"
    Column    |          Type          |                       Modifiers
-------------+------------------------ 
+-------------------------------------------------------
id          | integer                | not null default nextval 
('contacts_id_seq'::regclass)
record      | integer                |
type        | integer                |
value       | character varying(128) |
description | character varying(255) |
priority    | integer                |
itescotype  | integer                |
original    | integer                |
source      | integer                |
reference   | character varying(32)  |
quality     | integer                |
deleted     | boolean                | not null default false
searchable  | boolean                | not null default true
visible     | boolean                | not null default true
Indexes:
     "contacts_pkey" PRIMARY KEY, btree (id)
     "contacts_original_idx" btree (original)
     "contacts_quality_idx" btree (quality)
     "contacts_record_idx" btree (record)
     "contacts_source_reference_idx" btree (source, reference)
     "contacts_value_idx" btree (value)
Foreign-key constraints:
     "contacts_original_fkey" FOREIGN KEY (original) REFERENCES  
contacts(id)

se_companies=# \d addresses;
                                      Table "public.addresses"
     Column    |           Type           |                        
Modifiers
--------------+-------------------------- 
+--------------------------------------------------------
id           | integer                  | not null default nextval 
('addresses_id_seq'::regclass)
record       | integer                  |
address      | character varying(128)   |
extra        | character varying(32)    |
postalcode   | character varying(16)    |
postalsite   | character varying(64)    |
description  | character varying(255)   |
position     | point                    |
uncertainty  | integer                  | default 99999999
priority     | integer                  |
type         | integer                  |
place        | character varying(64)    |
floor        | integer                  |
side         | character varying(8)     |
housename    | character varying(64)    |
original     | integer                  |
source       | integer                  |
reference    | character varying(64)    |
quality      | integer                  |
deleted      | boolean                  | not null default false
searchable   | boolean                  | not null default true
visible      | boolean                  | not null default true
municipality | integer                  |
map          | boolean                  | not null default true
geocoded     | timestamp with time zone | default now()
Indexes:
     "addresses_pkey" PRIMARY KEY, btree (id)
     "addresses_lower_address_postalcode" btree (lower 
(address::text), lower(postalcode::text))
     "addresses_original_idx" btree (original)
     "addresses_record_idx" btree (record)
     "addresses_source_reference_idx" btree (source, reference)
Foreign-key constraints:
     "addresses_original_fkey" FOREIGN KEY (original) REFERENCES  
addresses(id)




view thread (4+ messages)  latest in thread

Message-ID: <0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com>
Permalink:  ../0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com/
Also on:    postgresql.org/message-id/0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: bendik.johansen@gmail.com
  Subject: Re: Problem with query, server totally unresponsive
  In-Reply-To: <0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com>
  References: <0B8E0D23-F08D-457D-B26F-1D475F8064F4@gmail.com>

* 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