From: Shaun Thomas <sthomas@peak6.com>
To: Kim A. Brandt <kimabrandt@gmx.de>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: LIMIT on partitioned-table!?
Date: Tue, 15 Feb 2011 08:49:37 -0600
Message-ID: <4D5A9281.4010605@peak6.com> (raw)
In-Reply-To: <4D5A8C6C.7080000@gmx.de>
References: <4D5A8C6C.7080000@gmx.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, kimabrandt@gmx.de
Subject: Re: LIMIT on partitioned-table!?
In-Reply-To: <4D5A9281.4010605@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox