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.
