From: | "Kim A(dot) Brandt" <kimabrandt(at)gmx(dot)de> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | sthomas(at)peak6(dot)com, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: LIMIT on partitioned-table!? |
Date: | 2011-02-16 07:24:06 |
Message-ID: | 4D5B7B96.7010701@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2011-02-16 07:33:22 | Re: high user cpu, massive SELECTs, no io waiting problem |
Previous Message | Robert Haas | 2011-02-16 02:41:24 | Re: [PERFORM] pgbench to the MAXINT |