Re: Benchmarking partitioning triggers and rules

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(at)gmail(dot)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.
>
>
>