From: Michael Okner <michael.okner@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Query planner ignoring constraints on partitioned tables when joining
Date: Wed, 17 Apr 2013 11:59:20 -0500
Message-ID: <7DF51702-0F6A-4571-80BB-188AAEF260DA@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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=
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: michael.okner@gmail.com
Subject: Re: Query planner ignoring constraints on partitioned tables when joining
In-Reply-To: <7DF51702-0F6A-4571-80BB-188AAEF260DA@gmail.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