pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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/
[ / \



view thread (33+ messages)  latest in thread

Message-ID: <Pine.LNX.4.61.0410011514150.8260@leda.steelsun.com>
Permalink:  ../Pine.LNX.4.61.0410011514150.8260@leda.steelsun.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.61.0410011514150.8260@leda.steelsun.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-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