Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E93581337B58 for ; Tue, 15 Feb 2011 10:49:45 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 96051-05 for ; Tue, 15 Feb 2011 14:49:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp2.peak6.com (smtp2.peak6.com [38.98.137.9]) by mail.postgresql.org (Postfix) with ESMTP id A9467133655C for ; Tue, 15 Feb 2011 10:49:37 -0400 (AST) X-AuditID: 0a02cb1e-b7bf8ae00000096d-45-4d5a92817b26 Message-ID: <4D5A9281.4010605@peak6.com> Date: Tue, 15 Feb 2011 08:49:37 -0600 From: Shaun Thomas Reply-To: sthomas@peak6.com Organization: Peak6 User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1 MIME-Version: 1.0 To: "Kim A. Brandt" CC: "pgsql-performance@postgresql.org" Subject: Re: LIMIT on partitioned-table!? References: <4D5A8C6C.7080000@gmx.de> In-Reply-To: <4D5A8C6C.7080000@gmx.de> Content-Type: text/plain; charset="ISO-8859-1"; format=flowed content-transfer-encoding: 7bit X-Brightmail-Tracker: AAAAAQAAAZE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.916 tagged_above=-10 required=5 tests=BAYES_00=-1.9, PLING_QUERY=0.994, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/389 X-Sequence-Number: 42527 On 02/15/2011 08:23 AM, Kim A. Brandt wrote: > does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it > is run on a partitioned-table or am I doing something wrong? It looks > as if postgres queries all partitions and then LIMITing the records > afterwards!? This results in a long (>3 minutes) running query. What > can I do to optimise this? Make sure you have constraint_exclusion set to 'on' in your config. Also, what are your checks for your partitions? You've got a pretty wide range in your 'ts' checks, so if you're using them as your partition definition, you're not helping yourself. The main issue might just be that you've used an order clause. LIMIT 1000 or not, even if it can restrict the result set based on your CHECK criteria, it'll still need to select every matching row from every matched partition, order the results, and chop off the first 1000. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@peak6.com ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email