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 E045BD1B498 for ; Sat, 24 Apr 2004 01:29:33 -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 27547-01 for ; Sat, 24 Apr 2004 01:29:32 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id F231CD1B43A for ; Sat, 24 Apr 2004 01:29:30 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id B3D2033CD9; Sat, 24 Apr 2004 01:29:30 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id B2CAA33C79 for ; Sat, 24 Apr 2004 01:29:30 -0300 (ADT) X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 11:40:56 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id C4084375F5 for ; Thu, 22 Apr 2004 11:40:38 -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 11:40:38 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.3) with LMTP; Thu, 22 Apr 2004 11:40:27 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 00B86D1CCB1 for ; Thu, 22 Apr 2004 11:40:27 -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 70106-10 for ; Thu, 22 Apr 2004 11:40:24 -0300 (ADT) X-Received: from minnie.omroep.nl (minnie.omroep.nl [145.58.30.4]) by svr1.postgresql.org (Postfix) with ESMTP id A4E13D1CACD for ; Thu, 22 Apr 2004 11:40:19 -0300 (ADT) X-Received: from tron.omroep.nl (tron.omroep.nl [145.58.31.20]) by minnie.omroep.nl (Postfix) with ESMTP id BFA351CD0D27; Thu, 22 Apr 2004 16:40:23 +0200 (CDT) X-Received: from michiel.omroep.nl (michiel.omroep.nl [145.58.67.10]) by tron.omroep.nl (Postfix) with ESMTP id B0F6610056BD; Thu, 22 Apr 2004 16:40:23 +0200 (CEST) X-Received: from michiel by michiel.omroep.nl with local (Exim 3.35 #1 (Debian)) id 1BGfNH-0007OS-00; Thu, 22 Apr 2004 16:40:23 +0200 Date: Thu, 22 Apr 2004 16:40:23 +0200 From: Michiel Meeuwissen To: Rod Taylor Cc: performance@postgresql.org Subject: Re: order by index, and inheritance Message-ID: <20040422144023.GI6870@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> <20040422110214.GH6870@michiel.omroep.nl> <1082641607.91360.2.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1082641607.91360.2.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:29:25 -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: <20040424012925.V42925@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/443 X-Sequence-Number: 6743 Rod Taylor wrote: > 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. Ok, I can get it working: select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 20) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 20) as B union select number,url from (select number,url from mm_videosources order by number desc limit 20) as C ) as TAB order by number desc limit 20; This indeeds performs good (about 10000 times faster then select number,url from mm_mediasources order by number desc limit 20) . But hardly beautiful, and quite useless too because of course I am now going to want to use an offset (limit 20 offset 20, you see..), which seems more or less impossible in this way, isn't it. select number,url from ( select number,url from (select number,url from only mm_mediasources order by number desc limit 100020) as A union select number,url from (select number,url from mm_audiosources order by number desc limit 100020) as B union select number,url from (select number,url from mm_videosources order by number desc limit 100020) as C ) as TAB order by number desc limit 20 offset 100000; This would be it, I think, but this performs, expectedly, quit bad again, though still 5 times faster then select url,number from mm_mediasources order by number desc limit 20 offset 100000; I'm thinking of dropping inheritance all together and using foreign keys or so for the extra fields, to simulate inheritance. That might perhaps work a whole lot better? Thanks anyway, -- Michiel Meeuwissen Mediapark C101 Hilversum +31 (0)35 6772979 nl_NL eo_XX en_US mihxil' [] ()