Hi,
I've been facing a very large (more than 15 seconds) planning time in a partitioned configuration. The amount of partitions wasn't completely crazy, around 500, not in the thousands. The problem was that there were nearly 1000 columns in the parent table (very special use case, there is a reason for this application for having these many columns). The check constraint was extremely simple (for each child, 1 column = 1 constant, always the same column).
As I was surprised by this very large planning time, I have been trying to study the variation of planning time against several parameters:
- number of columns
- number of children tables
- constraint exclusion's value (partition or off)
What (I think) I measured is that the planning time seems to be O(n^2) for the number of columns, and O(n^2) for the number of children tables.
Constraint exclusion had a limited impact on planning time (it added between 20% and 100% planning time when there were many columns).
I'd like to know if this is a known behavior ? And if I could mitigate it somehow ?
Attached is a zipped csv file containing the result of the tests for constraint_exclusion=partition, for children from 100 to 1000 in steps of 100, and for columns from 10 to 1590 in steps of 20.
A few values are a bit off-chart as this was done on my personal computer, and it was sometimes used for other things at the same time.
The tests were done with a parent table made of only integer columns, and every children having a check (col0=id_of_child) constraint (I can also provide the perl script, of course).
The test query was "SELECT * FROM parent_table WHERE col0=id_of_child_0". Replacing it with "SELECT col0 FROM parent_table WHERE col0=id_of_child_0" didn't change the planning time significantly: it was around 5% lower, but still O(n^2). This query returned nothing (every partition is empty).
I've also done an openoffice spreadsheet graphing all this, but as it's 130kB I won't send it before being told to do so :)
The computer running the tests was an Intel core i7 870. Postgresql was 9.0.3.
Anything else I could add ?
Cheers