From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Vitalii Tymchyshyn <vit@tym.im>
To: David G. Johnston <david.g.johnston@gmail.com>
To: GMail <mfwilson@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Partition Constraint Exclusion Limits
Date: Tue, 27 Oct 2015 21:59:12 -0500
Message-ID: <56303A00.20908@BlueTreble.com> (raw)
In-Reply-To: <CABWW-d3HS3tf1ANThLeDEuyZqh6hogRk4UWko7UTqz+mysdUYA@mail.gmail.com>
References: <7C9FB5CD-7AF6-4167-8CCA-2075BD9091E2@gmail.com>
<CAKFQuwYx1QJs-aKjZMHt6k85Mt5y3LbYW1A4RVWvm6mkHFPeyA@mail.gmail.com>
<CABWW-d3HS3tf1ANThLeDEuyZqh6hogRk4UWko7UTqz+mysdUYA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 10/27/15 3:33 PM, Vitalii Tymchyshyn wrote:
> BTW: May be it could be feasible in future to perform partition
> exclusion during the execution? This would be very neat feature.
True exclusion? probably not. The problem is you can't completely
exclude something based on any value that could change during execution.
There has been some work done on declarative partition specification,
where a given value would be fit to the exact partition it belong in.
IIRC that's currently stalled though.
One thing you could try would be to create an index on each partition
that would always be empty. IE, if you have a June 2015 partition, you
could:
CREATE INDEX ... ON( date_field ) WHERE date_field < '2015-6-1'::date OR
date_field >= '2015-7-1'::date;
Because the WHERE clause will never be true, that index will always be
empty, which will make probing it very fast. I suspect that might be
faster than probing a regular index on the date field, but you should
test it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: Jim.Nasby@BlueTreble.com, vit@tym.im, david.g.johnston@gmail.com, mfwilson@gmail.com
Subject: Re: Partition Constraint Exclusion Limits
In-Reply-To: <56303A00.20908@BlueTreble.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