From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Okner <michael.okner@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query planner ignoring constraints on partitioned tables when joining
Date: Thu, 18 Apr 2013 17:42:15 -0400
Message-ID: <25076.1366321335@sss.pgh.pa.us> (raw)
In-Reply-To: <7DF51702-0F6A-4571-80BB-188AAEF260DA@gmail.com>
References: <7DF51702-0F6A-4571-80BB-188AAEF260DA@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Michael Okner <michael.okner@gmail.com> writes:
> I've been able to reproduce the issue in a generic environment and posted the code to create this environment on my GitHub at https://github.com/mikeokner/pgsql_test. The query plans demonstrating this issue are pasted here: http://bpaste.net/show/92138/. I've poked around on IRC and no one seems to think this is normal behavior. Is it in fact a bug or is there something I should be doing to fix this behavior?
It's not a bug, though I can see why you'd like to wish it was.
What you've essentially got is
WHERE
(group_bbb_one.start_time = group_bbb_two.start_time)
AND
(group_bbb_one.start_time >= '2013-02-04 00:00:00'
AND group_bbb_one.start_time < '2013-02-05 00:00:00');
where the first clause is expanded out from the NATURAL JOIN, and the
rest is the way the parser interprets the references to the natural
join's outputs. So you have fixed constraints only on
group_bbb_one.start_time, which is why constraint exclusion triggers for
that table hierarchy and not the other one.
The only convenient way to fix this is to explicitly repeat the
constraints for each side of the join, eg
SELECT * FROM group_bbb_one NATURAL JOIN group_bbb_two
WHERE (group_bbb_one.start_time >= '2013-02-24 00:00:00'
AND group_bbb_one.start_time < '2013-02-25 00:00:00')
AND (group_bbb_two.start_time >= '2013-02-24 00:00:00'
AND group_bbb_two.start_time < '2013-02-25 00:00:00');
Now I can see why you might think this is a bug, because you don't have
to do it when the WHERE constraint is a simple equality. Then you
would have, in effect,
WHERE
(group_bbb_one.start_time = group_bbb_two.start_time)
AND
(group_bbb_one.start_time = '2013-02-04 00:00:00');
which the planner's equivalence-class mechanism replaces with
WHERE
(group_bbb_one.start_time = '2013-02-04 00:00:00')
AND
(group_bbb_two.start_time = '2013-02-04 00:00:00');
and so you get fixed constraints on both tables without having to write
it out explicitly. But that only works for equality conditions.
One could imagine adding planner logic that would make inferences of a
similar sort for equalities combined with inequalities, but it would be
vastly more complicated, and would provide useful results in vastly
fewer queries, than the equality-propagation logic. So don't hold your
breath waiting for something like that to happen.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, michael.okner@gmail.com
Subject: Re: Query planner ignoring constraints on partitioned tables when joining
In-Reply-To: <25076.1366321335@sss.pgh.pa.us>
* 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