pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planner ignoring constraints on partitioned tables when joining
3+ messages / 3 participants
[nested] [flat]
* Query planner ignoring constraints on partitioned tables when joining
@ 2013-04-17 16:59 Michael Okner <michael.okner@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Okner @ 2013-04-17 16:59 UTC (permalink / raw)
To: pgsql-performance
Hello all,
I'm running into an issue when joining between to tables that are partitioned by month. At this point I'm leaning towards it being a bug in the planner but it could be due to something I'm not doing properly as well. Each parent table is empty and has about 30 child tables, and there are between 2 and 10 mil rows total in each set of partitions. When selecting a particular day's data, indexes and constraint exclusion are used if queried individually and the results return in under a second. However, querying from the two tables inner/natural joined together with a single day in the where clause results in a full sequential scan on the second table, so the query takes a ridiculous amount of time. Changing the order of the tables in the join changes which table is fully scanned.
All child tables have been recently vacuum analyzed. I've played around with this every which way, and not been able to get the planner to make a more reasonable decision. I have several different boxes with varying physical specs running either CentOS 5.8 or 6.4, and Postgres 8.4.17, and they all exhibit the same behavior, so I've ruled out the possibility that it's related to a particular quirk in one database. I didn't notice the issue at first because the tables weren't large enough for it to cause any serious performance issues. Now that the tables have grown, queries involving a join no longer finish in any reasonable number of hours.
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?
Regards,
Mike=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query planner ignoring constraints on partitioned tables when joining
@ 2013-04-18 21:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Okner <michael.okner@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2013-04-18 21:42 UTC (permalink / raw)
To: Michael Okner <michael.okner@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query planner ignoring constraints on partitioned tables when joining
@ 2013-05-02 12:41 Simon Riggs <simon@2ndQuadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Simon Riggs @ 2013-05-02 12:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Okner <michael.okner@gmail.com>; pgsql-performance
On 18 April 2013 22:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
I'll take note that we need to make partitioning work for merge joins also.
On a more general note, it would be good to be able to look at the
starting value from the driving table of the join and use that as a
constraint in the scan on the second table. We rely on that mechanism
for nested loop joins, so we could do with that here also.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-05-02 12:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-17 16:59 Query planner ignoring constraints on partitioned tables when joining Michael Okner <michael.okner@gmail.com>
2013-04-18 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-05-02 12:41 ` Simon Riggs <simon@2ndQuadrant.com>
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