From: Kim A. Brandt <kimabrandt@gmx.de>
To: Marti Raudsepp <marti@juffo.org>
Cc: sthomas@peak6.com, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: LIMIT on partitioned-table!?
Date: Wed, 16 Feb 2011 08:24:06 +0100
Message-ID: <4D5B7B96.7010701@gmx.de> (raw)
In-Reply-To: <AANLkTikw3JH-gVOZX3=jjdc9MvjqYUzYbQd994iTtDH7@mail.gmail.com>
References: <4D5A8C6C.7080000@gmx.de>
<4D5A9281.4010605@peak6.com>
<4D5AD507.80800@gmx.de>
<AANLkTikw3JH-gVOZX3=jjdc9MvjqYUzYbQd994iTtDH7@mail.gmail.com>
Thank you Marti,
I will go with the ``reduced number of matched rows'' and naturally be waiting for postgres 9.1 expectantly.
Kind regards,
Kim
On 2011-02-15 22:13, Marti Raudsepp wrote:
> On Tue, Feb 15, 2011 at 21:33, Kim A. Brandt<kimabrandt@gmx.de> wrote:
>> removing the ORDER BY worked. But I am afraid to ask this. How can I order
>> by partition? It seams that the planner has picked a random(!?) order of
>> partition to select from. The returned records, from the selected partition,
>> are correctly sorted bythe index though.
>
> If a single query accesses more than one partition, PostgreSQL
> currently cannot read the values in index-sorted order. Hence with
> ORDER BY and LIMIT, PostgreSQL cannot return *any* results before it
> has read all matching rows and then sorted them. Adding a LIMIT
> doesn't help much. Your only bet is to reduce the number of matched
> rows, or make sure that you only access a single partition.
>
> Increasing work_mem may speed up the sort step if you're hitting the
> disk (EXPLAIN ANALYZE VERBOSE will tell you whether that's the case).
>
> This will change in PostgreSQL 9.1 which has a new Merge Append plan node.
>
> Regards,
> Marti
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: kimabrandt@gmx.de, marti@juffo.org
Subject: Re: LIMIT on partitioned-table!?
In-Reply-To: <4D5B7B96.7010701@gmx.de>
* 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