PostgreSQL: Partitioned Table vs Non Partitioned Table (Part 3) - Comment Page: 1
This article compares the speed and performance of queries between partitioned and non partitioned PostgreSQL tables. However, it is important to remember that the PostgreSQL tables partitioning has also another benefits, than the better performance on queries. More information about other benefits from the first part 'Howto create PostgreSQL table partitioning (Part 1)'.
This is comparision between partitioned and non partitioned PostgreSQL tables. The same tests were carried out with and without indices, because using the indices, it is no longer very meaningful example on table, which has one billion rows of data (if the table is not partitioned). This comparison is used an example of...
Hello,
Thanks for writing such a great tutorial on PostgreSQL Table Partition. I enjoy reading it very much.
I have some question in your tutorial.
Based on http://www.if-not-true-then-false.com/2009/12/performance-testing-between-partitioned-and-non-partitioned-postgresql-tables-part-3/
Example 3a. Select MAX impressions on 25 November 2010. (Day field indexed)
By using
How does PostgreSQL smart enough to know he need to look for table
Thanks!
Hi,
Thank you for your comment. Glad to hear that my writings are enjoyable to read.
And in response to your question:
Apparently, you mean the 3a query to table “impressions_by_day_partitioned”, not to table “impressions_by_day_non_partioned”?
The first part (Howto create PostgreSQL table partitioning (Part 1)) in section five, I tell about constraint_exclusion. Constraint_exclusion is a query optimization technique that improves performance for partitioned tables. I can show you a example:
So as you can see it loops thru all tables if constraint exclusion is off, and then query is much slower than query to single table. With constraint exclusion enabled, the PostgreSQL query planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query’s WHERE clause. When the planner can prove this, it excludes the partition from the query plan, as following:
And now same query to partitioned table works lightning fast compared to same query to single table.
And after this long example, the simple answer to your question is that: this logic (exclude not needed tables) is built into PostgreSQL Query Planner.
Thank you very much for your explanation.
I am now following your tutorial, to implement table partition in one of my application.
As my table rows grow into billions (very soon), I can feel that the query performance will some how drop.
Table partition seems to be the answer of my problem.
However, during implementation, I face some problem on it which I had mentioned in PostgreSQL general mailing list. I was wondering whether you are free enough to take a look, and input me some suggestion?
http://archives.postgresql.org/pgsql-general/2010-01/msg01184.php
Sorry if this is not the place to ask for your advice. But I cannot locate any of your contact.
Thank you very much.
Sounds like so that the partitioning is really a good solution in your situation, when the rows are growing and growing.
This is good place to ask advice for this problem, if I understand your problem rightly then my answer is very simple.
You have following in your trigger:
Change that RETURN NULL; to RETURN NEW; and then RETURNING * should work as expected.
So fixed version should look like that:
Did this solve your problem?
And true, here is not my contact details, but I would shortly add a contact form :)
But that will create additional problem. A duplicated entry will be created at parent table. To demo the problem :
(1) create database named “tutorial_partition”
(2) perform the following SQL query :
CREATE TABLE impressions_by_day (
advertiser_id SERIAL NOT NULL,
day DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, day)
);
CREATE OR REPLACE FUNCTION insert_table()
RETURNS void AS
$BODY$DECLARE
_impressions_by_day impressions_by_day;
BEGIN
INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING * INTO _impressions_by_day;
RAISE NOTICE ‘After insert, the returned advertiser_id is %’, _impressions_by_day.advertiser_id;
END;$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;
CREATE TABLE impressions_by_day_y2010m1ms2 (
PRIMARY KEY (advertiser_id, day),
CHECK ( day >= DATE ‘2010-01-01’ AND day = DATE ‘2010-01-01’ AND NEW.day < DATE '2010-03-01' ) THEN
INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Something wrong with the impressions_by_day_insert_trigger() function!';
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_impressions_by_day_trigger
BEFORE INSERT ON impressions_by_day
FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();
(3) execute
SELECT * FROM insert_table()
If you impressions_by_day_y2010m1ms2, everything looks fine.
However, if you exam impressions_by_day, it will contains duplicated item.
http://sites.google.com/site/yanchengcheok/Home/returningnew.PNG?attredirects=0
Any idea?
Thanks!
By the way, do you think it is possible to solve by
CREATE RULE
?
Not sure, cause I am not sure use CREATE RULE instead of TRIGGER, is the way to implement table partition.
Actually yes, that RETURN NEW cause new problems, because if the function returns NEW and not NULL value, then insert to table is run normally.
I also tried that CREATE RULE method, but it’s not working too, because it’s not possible write following RULES:
Because RETURNING on rule is not allowed, if there is some WHERE condition.
Error message is following:
Therefore it seems that PostgreSQL does not support this feature at all, but I wrote you a little (dirty) hack, which could get this to work. ;)
So I think following should work:
So this work little differently than normal PostgreSQL partitioning: main table has rule, which inserts to temp table. Then trigger function inserts that to right partition.
Maybe this works for you?
Thanks.
Instead applying your suggestion, I use a more straight forward approach.
Instead of calling INSERT… into unit, and hoping trigger function will handle the right job.
I will call insert directly on the child table.
— There is reason behind why we do not want to use trigger technique for table unit.
— Please refer to : http://archives.postgresql.org/pgsql-general/2010-01/msg01184.php
— INSERT INTO unit(fk_lot_id, cycle)
— VALUES(_lotID, _cycle) RETURNING unit_id INTO _unit_id;
unit_table_index = _lotID;
unit_table_name = ‘unit_’ || _lotID;
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
EXECUTE ‘CREATE TABLE ‘ || quote_ident(unit_table_name) || ‘
(
unit_id bigserial NOT NULL,
fk_lot_id bigint NOT NULL,
CHECK (fk_lot_id = ‘ || (unit_table_index) || ‘),
CONSTRAINT pk_unit_’ || unit_table_index || ‘_id PRIMARY KEY (unit_id),
CONSTRAINT fk_lot_’ || unit_table_index || ‘_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
) INHERITS (unit);’;
EXECUTE ‘CREATE INDEX fk_lot_’ || unit_table_index || ‘_id_idx ON ‘ || quote_ident(unit_table_name) || ‘(fk_lot_id);’;
END IF;
EXECUTE ‘INSERT INTO ‘ || quote_ident(unit_table_name) || ‘(fk_lot_id, cycle) VALUES (‘ || _lotID || ‘,’ || _cycle || ‘) RETURNING unit_id’
INTO _unit_id;
_unit.unit_id = _unit_id;
_unit.fk_lot_id = _lotID;
_unit.cycle = _cycle;
Really nice to hear that you got the problem resolved and got it to work, just as you wanted. :)
Inserting directly on the child table is very good and robust solution in this case.
well not if you depend on the parent table to keep the id unique on all child tables
You can always use one sequence nextval to make sure that you have always unique id, but normal partitioning setup this shouldn’t be problem.
Possible to get updated stats with a recent version of postgres? :)