From: Christian Fowler <spider@steelsun.com>
To: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 1 Oct 2004 15:25:06 -0400 (EDT)
Message-ID: <Pine.LNX.4.61.0410011514150.8260@leda.steelsun.com> (raw)
In-Reply-To: <002801c4a7d0$66797ad0$3402a8c0@shanepc>
References: <002801c4a7d0$66797ad0$3402a8c0@shanepc>
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/
[ / \
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-admin@postgresql.org
Cc: spider@steelsun.com, shanew@skinnycorp.com
Subject: Re: PLEASE GOD HELP US!
In-Reply-To: <Pine.LNX.4.61.0410011514150.8260@leda.steelsun.com>
References: <002801c4a7d0$66797ad0$3402a8c0@shanepc> <Pine.LNX.4.61.0410011514150.8260@leda.steelsun.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