| 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: | Whole Thread | Raw Message | 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 |