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 <timuc...@gmail.com> wrote: > 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. > > >
