From: Michael Paesold <mpaesold@gmx.at>
To: Shane | SkinnyCorp <shanew@skinnycorp.com>
To: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 1 Oct 2004 23:22:39 +0200
Message-ID: <045401c4a7fc$c9433a80$ad01a8c0@zaphod> (raw)
References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc>
Shane | SkinnyCorp wrote:
> Okay, just so no one posts about this again...
>
> the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> with a status of '5' to the top of the list... it is NOT meant to only
> grab
> threads where the status = 5. Oh and believe me, when I take this out of
> the query, it CERTAINLY doesn't add any more than possible 1/4 of a
> millesecond to the speed of the SELECT statement.
>
> :/
Perhaps I missed it, but you did not yet send the output of the explain of
this query, did you?
Could you please do:
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC,
t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY
t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25
OFFSET 0;
(and post the results here)
The first one will certainly do a sequential scan, the last one will use an
index if available. For the second you will need a partial index on
lastreply with a where clause WHERE status=5, I believe. So a solution would
perhaps be to use two queries, one with WHERE t.status=5, another without.
Make both use an index. Then combine them in your application. Of course
this can only work if you do not need an offset other then 0.
Also, did you check that your system does not swap (using vmstat)?
Best Regards,
Michael Paesold
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: mpaesold@gmx.at, shanew@skinnycorp.com
Subject: Re: PLEASE GOD HELP US!
In-Reply-To: <045401c4a7fc$c9433a80$ad01a8c0@zaphod>
References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> <045401c4a7fc$c9433a80$ad01a8c0@zaphod>
* 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