From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Benchmarking partitioning triggers and rules |
Date: | 2015-03-08 22:44:13 |
Message-ID: | CAGuHJrP7-KB_H3eonGmWpvvi3AwRkgp0X5A4bvv8oPkTtntcsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I wrote a quick benchmarking script to test various partitioning
strategies. The code is here.
https://gist.github.com/timuckun/954ab6bdce36fa14bc1c
I was astonished at the variability of the timings between the different
variations.
The test data contained 270K records. I did a simple insert into without
any triggers, with three different trigger variations and with a rule. The
results were like this
clean 0.000000 0.000000 0.000000 ( 3.119498)
func_1 0.000000 0.000000 0.000000 ( 7.435094)
func_2 0.000000 0.000000 0.000000 ( 28.427617)
func_3 0.000000 0.000000 0.000000 ( 18.348554)
Rule 0.000000 0.000000 0.000000 ( 2.901931)
A clean insert 3.1 seconds, putting a rule took less time!
A simple insert into table_name values (NEW.*) doubled the time it takes to
insert the records. Using an EXECUTE with an interpolated table name took
TEN TIMES LONGER!. Making a slight change to the EXECUTE took a third off
the execution time WTF???
This has left me both baffled and curious. If changing little things like
this makes a huge difference what else can I do to make this even faster?
Would using a different language help? Is Javasscript, Python or Perl
faster? Is there some other syntax I can use? I tried this
EXECUTE 'INSERT INTO ' || quote_ident(partition_name) || ' VALUES (' ||
NEW.* || ')' but that gave me an error.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2015-03-08 22:57:55 | Re: Postgres and data warehouses |
Previous Message | Bill Moran | 2015-03-08 16:09:27 | Re: Postgres and data warehouses |