Skip site navigation (1) Skip section navigation (2)

Re: order by index, and inheritance

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Michiel Meeuwissen <Michiel(dot)Meeuwissen(at)omroep(dot)nl>
Cc: performance(at)postgresql(dot)org
Subject: Re: order by index, and inheritance
Date: 2004-04-22 13:46:48
Message-ID: 1082641607.91360.2.camel@jester (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote:
> Rod Taylor <pg(at)rbt(dot)ca> wrote:
> > The scan is picking the best method for grabbing everything within the
> > table, since it is not aware that we do not require everything.
> 
> Hmm. That is a bit silly. Why does it use the index if select only from
> mm_mediasources?
> 
> > You can explicitly tell it what you want to do via:
> > 
> > SELECT *
> >   FROM (SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20 
> >   UNION SELECT * FROM <subtable> ORDER BY number DESC LIMIT 20) AS tab
> >  ORDER BY number DESC LIMIT 20
> 
> I think you meant 'only mm_mediasources', and btw order by and limit are not
> accepted before union, so the above query does not compile.

Yes, I did mean only. Try putting another set of brackets around the
selects to get ORDER BY, etc. accepted. You can add another layer of
subselects in the from if that doesn't work.


In response to

Responses

pgsql-performance by date

Next:From: Eduardo AlmeidaDate: 2004-04-22 13:59:10
Subject: Re: [PERFORM] MySQL vs PG TPC-H benchmarks
Previous:From: Grega BremecDate: 2004-04-22 13:42:49
Subject: Re: [PERFORM] MySQL vs PG TPC-H benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group