Re: Benchmarking partitioning triggers and rules

I am using postgres 9.4, the default install with "brew install postgres,
no tuning at all. BTW if I use postgres.app application the benchmarks run
twice as slow!

Why do you think there is such dramatic difference between

EXECUTE 'INSERT INTO ' || quote_ident(partition_name) || ' SELECT
($1).*' USING NEW ;

and

EXECUTE 'INSERT INTO ' || quote_ident(partition_name) || ' VALUES(
($1).*)' USING NEW ;

One is thirty percent faster than the other. Also is there an even better
way that I don't know about.

On Thu, Mar 12, 2015 at 10:42 AM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com
> wrote:

> On 11.3.2015 21:43, Tim Uckun wrote:
> > 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?
>
> I think to actually give you a meaningful response, we really need more
> details about your configuration - what PostgreSQL version are you
> using, what configuration have you changed from the defaults and such.
>
> Anyway, you're right that triggers are not cheap. The numbers I get on
> the development version with a bit of tuning look like this:
>
> INSERT (direct) 1.5 sec
> INSERT 4.0 sec
> INSERT (EXECUTE) 11.5 sec
>
> So it's ~ what you've measured. Rules have the lowest overhead, but also
> there's a lot of tricky places.
>
> There's not much you can do, except for inserting the data directly into
> the right partition (without any triggers).
>
>
> --
> Tomas Vondra http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>