X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 77571D1B470 for ; Sat, 24 Apr 2004 01:28:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03923-10 for ; Sat, 24 Apr 2004 01:28:51 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 2FF7ED1B43A for ; Sat, 24 Apr 2004 01:28:51 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id CF15C341C6; Sat, 24 Apr 2004 01:28:50 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id CC53D33C79 for ; Sat, 24 Apr 2004 01:28:50 -0300 (ADT) X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 10:51:19 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id B8E5E37452 for ; Thu, 22 Apr 2004 10:50:57 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Thu, 22 Apr 2004 10:50:57 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 10:46:54 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DE67CD1C50D for ; Thu, 22 Apr 2004 10:46:53 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 58271-05 for ; Thu, 22 Apr 2004 10:46:55 -0300 (ADT) X-Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 08D54D1BACD for ; Thu, 22 Apr 2004 10:46:51 -0300 (ADT) X-Received: from [134.22.70.203] (dyn-70-203.tor.dsl.tht.net [134.22.70.203]) by tht.net (Postfix) with ESMTP id 329A376A31; Thu, 22 Apr 2004 09:46:56 -0400 (EDT) Subject: Re: order by index, and inheritance From: Rod Taylor To: Michiel Meeuwissen Cc: performance@postgresql.org In-Reply-To: <20040422110214.GH6870@michiel.omroep.nl> References: <20040419113013.GA32186@michiel.omroep.nl> <1082455997.57436.10.camel@jester> <20040420104518.GF1618@michiel.omroep.nl> <1082461767.57436.93.camel@jester> <20040422110214.GH6870@michiel.omroep.nl> Content-Type: text/plain Message-Id: <1082641607.91360.2.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Thu, 22 Apr 2004 09:46:48 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Sat, 24 Apr 2004 01:28:46 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-performance@postgresql.org ReSent-Subject: Re: [PERFORM] order by index, and inheritance ReSent-Message-ID: <20040424012846.R42925@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/422 X-Sequence-Number: 6722 On Thu, 2004-04-22 at 07:02, Michiel Meeuwissen wrote: > Rod Taylor 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 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.