From: Michael Okner <michael.okner@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_B108FE47-F972-49D7-BB5E-58981C13AFD2"
Subject: Query planner ignoring constraints on partitioned tables when joining
Message-Id: <7DF51702-0F6A-4571-80BB-188AAEF260DA@gmail.com>
Date: Wed, 17 Apr 2013 11:59:20 -0500
To: pgsql-performance@postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 6.3 \(1503\))
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--Apple-Mail=_B108FE47-F972-49D7-BB5E-58981C13AFD2
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

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=

--Apple-Mail=_B108FE47-F972-49D7-BB5E-58981C13AFD2
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html =
charset=3Dus-ascii"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ; =
font-family: 'Helvetica'; font-size: 12px; color: rgb( 0,  0,  0); =
"><span style=3D"font-family: 'Helvetica'; font-size: 12px; color: rgb( =
0,  0,  0)">Hello all,<div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>I've been able to reproduce the issue in =
a generic environment and posted the code to create this environment on =
my GitHub at <a =
href=3D"https://github.com/mikeokner/pgsql_test">https://github.com/mikeok=
ner/pgsql_test</a>. The query plans demonstrating this issue are pasted =
here:&nbsp;<a =
href=3D"http://bpaste.net/show/92138/">http://bpaste.net/show/92138/</a>. =
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?</div><div><br></div><div>Regards,</div><div>Mike</div></span></b=
ody></html>

--Apple-Mail=_B108FE47-F972-49D7-BB5E-58981C13AFD2--