From: Gregory Stark <stark@enterprisedb.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: Andreas Haumer <andreas@xss.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
Date: Mon, 30 Apr 2007 16:35:55 +0100
Message-ID: <87slah98o4.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <87fy6ige74.fsf@meuh.mnc.lan>
References: <4635D678.3050205@xss.co.at>
<87ps5mgghp.fsf@meuh.mnc.lan>
<4635EF3A.1070204@xss.co.at>
<87fy6ige74.fsf@meuh.mnc.lan>
"Guillaume Cottenceau" <gc@mnc.ch> writes:
> I think this is the last claimed point which is incorrect. Pg has
> no general guarantee the partitions actually create a disjoint
> set, even with the CHECK constraints. Pg can only optimize by
> avoiding scanning the partitions inside which no satisfactory
> data could be found by the CHECK constraint, but I think it's not
> possible (too complicated) to infer that any found row in your
> other partitions would not be in the final resultset because of
> 1. the query's resultset order 2. the limit 3. the actual
> conditions in the CHECK constraints (there is no direct way to
> see that timestamps in your 200704 partition are greater than
> timsteamp in your 200601 partition).
I think the answer is that yes there are a number of query transformations
that could be done for partitioned tables that we're not doing currently.
Generally speaking we need to look at each type of plan node and figure out
whether it can usefully be pushed down below the Append node and how we can
determine when that can be done.
So if each arm of the Append was already in order we could easily push the
LIMIT inside the Append (and duplicating the work above the Append). But that
wouldn't save us any work because we only generate rows from the partitions as
they're needed anyways.
In your example we could save work because the Sort needs all the rows before
it starts. So we could sort each arm and apply the limit before passing it up
to the outer Sort. That might save work in this case.
But figuring out when that's less work than just sorting all of them is
tricky. If the LIMIT is 1,000 and you have ten arms of 1,000 tuples each then
you aren't going to save any work doing this. But if the LIMIT is 1 and you
have few arms with many tuples then you could save a lot of work.
Actually I happen to have been reading up on algorithms related to this this
weekend. It's possible to implement a LimitUnsorted in linear-time which would
fetch the first n records according to some sort key without actually sorting
the records. That might make it more worthwhile.
In short. Yes, there are a lot of optimizations possible around partitioned
tables that we don't do either because it's not clear how to tell when they're
worthwhile or because the code just isn't there yet.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, gc@mnc.ch, andreas@xss.co.at
Subject: Re: Query performance problems with partitioned tables
In-Reply-To: <87slah98o4.fsf@oxford.xeocode.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