X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A4DEB11F61F0 for ; Thu, 27 Apr 2006 15:12:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 03936-05 for ; Thu, 27 Apr 2006 15:12:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 8D8B211F61E8 for ; Thu, 27 Apr 2006 15:12:04 -0300 (ADT) Received: from hotmail.com (bay101-f19.bay101.hotmail.com [64.4.56.29]) by svr4.postgresql.org (Postfix) with ESMTP id 5BFFD5B5DF1 for ; Thu, 27 Apr 2006 18:12:04 +0000 (GMT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 27 Apr 2006 11:12:02 -0700 Message-ID: Received: from 64.4.56.200 by by101fd.bay101.hotmail.msn.com with HTTP; Thu, 27 Apr 2006 18:12:01 GMT X-Originating-IP: [84.210.10.106] X-Originating-Email: [bealach_na_bo@hotmail.com] X-Sender: bealach_na_bo@hotmail.com From: "Bealach-na Bo" To: pgsql-performance@postgresql.org Subject: Why so slow? Date: Thu, 27 Apr 2006 18:12:01 +0000 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 27 Apr 2006 18:12:02.0437 (UTC) FILETIME=[153EB350:01C66A26] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.693 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, MSGID_FROM_MTA_HEADER, SPF_SOFTFAIL X-Spam-Level: X-Archive-Number: 200604/652 X-Sequence-Number: 18736 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