Re: PLEASE GOD HELP US! - Mailing list pgsql-admin

From Scott Marlowe
Subject Re: PLEASE GOD HELP US!
Date
Msg-id 1096774761.32732.11.camel@localhost.localdomain
Whole thread Raw
In response to Re: PLEASE GOD HELP US!  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List pgsql-admin
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?


pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Installing postgresql-8.0.0beta3
Next
From: Michael Long
Date:
Subject: Re: Installing postgresql-8.0.0beta3