pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@qwest.net>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Sat, 02 Oct 2004 21:39:21 -0600
Message-ID: <1096774761.32732.11.camel@localhost.localdomain> (raw)
In-Reply-To: <20041002143450.V11227@megazone.bigpanda.com>
References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc>
	<1096691616.2611.22.camel@localhost.localdomain>
	<20041002080923.C99145@megazone.bigpanda.com>
	<1096751454.2611.33.camel@localhost.localdomain>
	<20041002143450.V11227@megazone.bigpanda.com>

On Sat, 2004-10-02 at 15:42, Stephan Szabo wrote:
> On Sat, 2 Oct 2004, Scott Marlowe wrote:
> > So would a union give good performance?  Just union the first 25 or less
> > with status=5 with the rest, using a 1 and 0 in each union to order by
> > first?  Hopefully the indexes would then be used.
> 
> You'd want to use union all I think and you may have to put limits on the
> unioned arms to get good behavior. I think the expression index would
> probably work without changing the query (at least for relatively low
> offsets) at the cost of having an extra index to maintain.
> 

I wonder if this would offer any improvement:

select * from (
	(SELECT 2 as a,* FROM thread_listing AS t 
	where t.status=5 
	ORDER BY t.lastreply desc limit 25) 
union 
	(SELECT 1 as a,* FROM thread_listing AS t 
	where t.status<>5 
	order by t.lastreply desc limit 25)
) as p 
order by p.a desc, p.lastreply desc limit 25;

Or is there a moving offset we have to deal with here?




view thread (33+ messages)  latest in thread

Message-ID: <1096774761.32732.11.camel@localhost.localdomain>
Permalink:  ../1096774761.32732.11.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1096774761.32732.11.camel@localhost.localdomain

 · 

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: smarlowe@qwest.net, sszabo@megazone.bigpanda.com, shanew@skinnycorp.com
  Subject: Re: PLEASE GOD HELP US!
  In-Reply-To: <1096774761.32732.11.camel@localhost.localdomain>
  References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> <1096691616.2611.22.camel@localhost.localdomain> <20041002080923.C99145@megazone.bigpanda.com> <1096751454.2611.33.camel@localhost.localdomain> <20041002143450.V11227@megazone.bigpanda.com> <1096774761.32732.11.camel@localhost.localdomain>

* 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