Re: Benchmarking partitioning triggers and rules
| От | Tim Uckun |
|---|---|
| Тема | Re: Benchmarking partitioning triggers and rules |
| Дата | |
| Msg-id | CAGuHJrNQZEUdFSuSFH2ARA2=fYsOp8QTnFVXGahczaybCJ+SEg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Benchmarking partitioning triggers and rules (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
| Ответы |
Re: Benchmarking partitioning triggers and rules
|
| Список | pgsql-general |
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 ;
On Thu, Mar 12, 2015 at 10:42 AM, Tomas Vondra <tomas.vondra@2ndquadrant.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
В списке pgsql-general по дате отправления: