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 EDFD9D1C4EC for ; Sat, 24 Apr 2004 01:32:23 -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 27329-04 for ; Sat, 24 Apr 2004 01:32:22 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id C4428D1B43A for ; Sat, 24 Apr 2004 01:32:21 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 8068434356; Sat, 24 Apr 2004 01:32:21 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 7F68E34323 for ; Sat, 24 Apr 2004 01:32:21 -0300 (ADT) X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 08:05:48 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 3B82836B79 for ; Thu, 22 Apr 2004 08:05:41 -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 08:05:41 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 08:02:15 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B70F1D1DFA5 for ; Thu, 22 Apr 2004 08:02:15 -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 98010-06 for ; Thu, 22 Apr 2004 08:02:15 -0300 (ADT) X-Received: from minnie.omroep.nl (minnie.omroep.nl [145.58.30.4]) by svr1.postgresql.org (Postfix) with ESMTP id 86711D1E0FE for ; Thu, 22 Apr 2004 08:02:12 -0300 (ADT) X-Received: from tron.omroep.nl (tron.omroep.nl [145.58.31.20]) by minnie.omroep.nl (Postfix) with ESMTP id DA6601CF5524; Thu, 22 Apr 2004 13:02:14 +0200 (CDT) X-Received: from michiel.omroep.nl (michiel.omroep.nl [145.58.67.10]) by tron.omroep.nl (Postfix) with ESMTP id 926C710056BD; Thu, 22 Apr 2004 13:02:14 +0200 (CEST) X-Received: from michiel by michiel.omroep.nl with local (Exim 3.35 #1 (Debian)) id 1BGbyA-0006Vo-00; Thu, 22 Apr 2004 13:02:14 +0200 Date: Thu, 22 Apr 2004 13:02:14 +0200 From: Michiel Meeuwissen To: Rod Taylor Cc: performance@postgresql.org Subject: Re: order by index, and inheritance Message-ID: <20040422110214.GH6870@michiel.omroep.nl> Reply-To: Michiel Meeuwissen References: <20040419113013.GA32186@michiel.omroep.nl> <1082455997.57436.10.camel@jester> <20040420104518.GF1618@michiel.omroep.nl> <1082461767.57436.93.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1082461767.57436.93.camel@jester> Organization: Publieke Omroep []() User-Agent: Mutt/1.5.5.1+cvs20040105i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Sat, 24 Apr 2004 01:32:14 -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: <20040424013214.A42925@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/444 X-Sequence-Number: 6744 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. I can't figure out any acceptable work-around. Even if something as the above would work, it still would be hardly elegant, and you can as well have no support for inheritance (actually, you can _better_ have no inheritance, because at least it is clear what works then). Michiel btw. Why are these messages not appearing on the list itself? -- Michiel Meeuwissen Mediapark C101 Hilversum +31 (0)35 6772979 nl_NL eo_XX en_US mihxil' [] ()