Hey I hate to bump my post but I would really appreciate some input on this benchmark. I am very alarmed that adding a very simple partitioning trigger slows the insert speed by an order of magnitude. Is there any way to speed this up?
On Mon, Mar 9, 2015 at 11:44 AM, Tim Uckun <timuckun@xxxxxxxxx> wrote:
Would using a different language help? Is Javasscript, Python or Perl faster? Is there some other syntax I can use? I tried thisThis 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?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???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 thisI wrote a quick benchmarking script to test various partitioning strategies. The code is here.I was astonished at the variability of the timings between the different variations.
https://gist.github.com/timuckun/954ab6bdce36fa14bc1c
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!
EXECUTE 'INSERT INTO ' || quote_ident(partition_name) || ' VALUES (' || NEW.* || ')' but that gave me an error.
