From: Bealach-na Bo <bealach_na_bo@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Why so slow?
Date: Thu, 27 Apr 2006 18:12:01 +0000
Message-ID: <BAY101-F19376EDCAA36CB81EBA790ADBD0@phx.gbl> (raw)
Hi folks,
Sorry to be bringing this up again, but I'm stumped by this problem
and hope you can shed some light on it.
I'm running postgresql 8.0 on a RLE4 server with 1.5 GB of RAM and a
Xenon 2 GHz CPU. The OS is bog standard and I've not done any kernel
tuning on it. The file system is also bog standard ext3 with no raid
of any kind. I know I could improve this aspect of the set up with
more disks and raid 0+1 etc, but the lack of performance that I'm
experiencing is not likely to be attributable to this sort of
thing. More likely it's my bad understanding of Postgresql - I hope
it's my bad understanding of Postgresql!!
My database is very simple and not by the book (normal forms etc. are
not all as they should be). My biggest table, by a factor of 3000 or
so is one of 4 tables in my tiny database. It looks like this
\d job_log
Table "job_log"
Column | Type | Modifiers
----------------+-----------------------------+--------------------------------------------------
job_log_id | integer | not null default
nextval('job_log_id_seq'::text)
first_registry | timestamp without time zone |
customer_name | character(50) |
node_id | integer |
job_type | character(50) |
job_name | character(256) |
job_start | timestamp without time zone |
job_timeout | interval |
job_stop | timestamp without time zone |
nfiles_in_job | integer |
status | integer |
error_code | smallint |
file_details | text |
Indexes:
"job_log_id_pkey" PRIMARY KEY, btree (job_log_id)
"idx_customer_name_filter" btree (customer_name)
"idx_job_name_filter" btree (job_name)
"idx_job_start_filter" btree (job_start)
"idx_job_stop_filter" btree (job_stop)
Check constraints:
"job_log_status_check" CHECK (status = 0 OR status = 1 OR status = 8 OR
status = 9)
Foreign-key constraints:
"legal_node" FOREIGN KEY (node_id) REFERENCES node(node_id)
The node table is tiny (2500 records). What I'm pulling my hair out
over is that ANY Query, even something as simple as select count(*)
form job_log takes of the order of tens of minutes to complete. Just
now I'm trying to run an explain analyze on the above query, but so
far, it's taken 35min! with no result and there is a postgres process at
the top of top
What am I doing wrong??
Many thanks,
Bealach
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: bealach_na_bo@hotmail.com
Subject: Re: Why so slow?
In-Reply-To: <BAY101-F19376EDCAA36CB81EBA790ADBD0@phx.gbl>
* 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