X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3DB6E9FBAD4 for ; Fri, 25 Aug 2006 11:46:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 70927-06 for ; Fri, 25 Aug 2006 11:45:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id B56B09FBAD1 for ; Fri, 25 Aug 2006 11:45:56 -0300 (ADT) Received: from alvh.no-ip.org (201-221-200-187.bk11-dsl.surnet.cl [201.221.200.187]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k7PEjrI5029092; Fri, 25 Aug 2006 07:45:54 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 7E2DCC2DC58; Fri, 25 Aug 2006 10:45:30 -0400 (CLT) Date: Fri, 25 Aug 2006 10:45:30 -0400 From: Alvaro Herrera To: "Joshua D. Drake" Cc: Tom Lane , Sriram Dandapani , "Pgsql-Performance (E-mail)" Subject: Re: select max(column) from parent table very slow Message-ID: <20060825144530.GA14622@alvh.no-ip.org> Mail-Followup-To: "Joshua D. Drake" , Tom Lane , Sriram Dandapani , "Pgsql-Performance (E-mail)" References: <6992E470F12A444BB787B5C937B9D4DF057500BF@ca-mail1.cis.local> <44EE4DA1.5060701@commandprompt.com> <18394.1156480407@sss.pgh.pa.us> <44EE87BB.4050104@commandprompt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <44EE87BB.4050104@commandprompt.com> User-Agent: Mutt/1.5.12-2006-07-14 X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Fri, 25 Aug 2006 07:45:54 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.338 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/404 X-Sequence-Number: 20589 Joshua D. Drake wrote: > Tom Lane wrote: > >"Joshua D. Drake" writes: > >>Sriram Dandapani wrote: > >>>Parent table has a column say column1 which is indexed (parent table and > >>>all child tables are indexed on that column) > > > >>In older versions of postgresql that would scan the whole table. In 8.1 > >>and above it doesn't. However, I am guess that since this is a > >>partitioned table the planner isn't smart enough to just perform the > >>query on each child and a max on the set that is returned. > > > >It is not. Feel free to submit a patch for planagg.c ... > > I think my patch to pgbench may have set your expectations of me a bit > high ;)... Actually I think this is the perfect opportunity for you -- a patch that not only was absolutely unexpected, undiscussed, and posted without previous warning, but one that you were actually asked about! And weren't you recently joking about giving Tom nightmares by sending patches to the optimizer? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support