X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EAAC332A28A for ; Fri, 1 Oct 2004 20:25:18 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90406-10 for ; Fri, 1 Oct 2004 19:25:16 +0000 (GMT) Received: from leda.steelsun.com (dsl093-240-204.ral1.dsl.speakeasy.net [66.93.240.204]) by svr1.postgresql.org (Postfix) with ESMTP id ECCE332A283 for ; Fri, 1 Oct 2004 20:25:13 +0100 (BST) Received: from leda.steelsun.com (localhost [127.0.0.1]) by leda.steelsun.com (8.12.11/8.12.8) with ESMTP id i91JP69X009043; Fri, 1 Oct 2004 15:25:06 -0400 Received: from localhost (cfowler@localhost) by leda.steelsun.com (8.12.11/8.12.11/Submit) with ESMTP id i91JP6KE009040; Fri, 1 Oct 2004 15:25:06 -0400 X-Authentication-Warning: leda.steelsun.com: cfowler owned process doing -bs Date: Fri, 1 Oct 2004 15:25:06 -0400 (EDT) From: Christian Fowler X-X-Sender: cfowler@leda.steelsun.com To: Shane|SkinnyCorp Cc: PgSQL ADMIN Subject: Re: PLEASE GOD HELP US! In-Reply-To: <002801c4a7d0$66797ad0$3402a8c0@shanepc> Message-ID: References: <002801c4a7d0$66797ad0$3402a8c0@shanepc> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/25 X-Sequence-Number: 15019 Hi Shane, As many others have alluded to - performance like this is almost always attributable to your queries not using an index. Be it on Oracle, Mysql, or postgres, i have seen this problem popup often. Also, could you tell us what language you are using, and if you are using a DB abstraction layer? On to the particulars: > # WEBSITE # > > # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES > > 8 Queries Totaling 10.7413 Seconds Since one query is taking 90% of the time, it clearly is the first cuplrit: > SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 Your SQL here seems what I would consider not typical. I would write it as: SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply desc LIMIT 25 OFFSET 0; Run that from a psql shell, and see if that speed things up. If not, run: db=> EXPLAIN ANALYSE SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply desc LIMIT 25 OFFSET 0; and db=> \d thread_listing And send it to the list. You are in good shape I think, and porting won't be necessary. I've used many db's and postgres is my favorite by far. I'd say you've made a good choice ;-) [ \ / [ >X< spider@steelsun.com | http://www.steelsun.com/ [ / \