From: Marc Cousin <cousinmarc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: inheritance: planning time vs children number vs column number
Date: Mon, 28 Feb 2011 10:38:04 +0100
Message-ID: <201102281038.04945.cousinmarc@gmail.com> (raw)
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
Attachments:
result.csv.zip (application/zip, 5.0K) download
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: cousinmarc@gmail.com
Subject: Re: inheritance: planning time vs children number vs column number
In-Reply-To: <201102281038.04945.cousinmarc@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