Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B5F541337BF9 for ; Thu, 7 Oct 2010 11:32:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 21895-05-4 for ; Thu, 7 Oct 2010 14:32:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by mail.postgresql.org (Postfix) with ESMTP id C72551337BFE for ; Thu, 7 Oct 2010 11:32:42 -0300 (ADT) Received: from [192.168.1.7] (static-71-179-240-8.bltmmd.fios.verizon.net [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0MJ0M1-1P1Nc91OB7-002Fm3; Thu, 07 Oct 2010 16:32:41 +0200 Message-ID: <4CADDA06.5070002@2ndquadrant.com> Date: Thu, 07 Oct 2010 10:32:38 -0400 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Vincenzo Romano CC: Robert Haas , Heikki Linnakangas , Simon Riggs , pgsql-hackers@postgresql.org Subject: Re: On Scalability References: <1280423526.8613.36.camel@jd-desktop.unknown.charter.com> <1280425184.8613.41.camel@jd-desktop.unknown.charter.com> <1280427410.8613.44.camel@jd-desktop.unknown.charter.com> <4CAD769D.9090301@enterprisedb.com> <1286437290.2304.192.camel@ebony> <4CAD7BED.4050009@enterprisedb.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:Bi/73yqbPBa2Fd+lywPZ42Gc4HwMgBMzVHelIui+TLQ F7Pytg7LYy+4IH6SyIiW+RDzjkjXU6ZZ/guguxc1Jwup3b34I6 5eqMdzzQp+6k46yAYfW9j+IAobP6fMDfrgRJnlTOfxmANen7eC O71TW0ine5xYhUx/wliO7r6pEJpdfm4E96qMmnPuk3s+IK26l9 2EcTO+t+8KcyjGup4mzp6G1TqEnxgKBCHk8GiB2v4E= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/460 X-Sequence-Number: 171500 Vincenzo Romano wrote: > I see the main problem in the way the planner "understands" which partition > is useful and which one is not. > Having the DDL supporting the feature could just be syntactic sugar > if the underlying mechanism is inadequate. > You have the order of this backwards. In order to do better than the way the current scheme is implemented, the optimizer needs higher quality metadata about the structure of the partitions to work with. Right now, it's inferring them from the CHECK constraints, which requires the whole theorem-proving bit Tom mentioned. That's never going to get any more algorithmically efficient than it already is. If the DDL that created the partitions also made better quality metadata available about the structure of the partitions, at that point it would be possible to also do better in how the optimizer pruned partitions to consider too. If the list it has was known to be in a particular structured/sorted order, the optimizer could do a binary search to find relevant partitions, rather than the linear scan required right now. Until that work is done, any other improvement attempts are doomed to fail. That's the point Robert was trying to make to you. And the fact Oracle does this is why it's able to scale to high partition counts better than PostgreSQL can. You can read more about the work that was being done here at http://wiki.postgresql.org/wiki/Table_partitioning -- Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us