Benchmarking partitioning triggers and rules

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Benchmarking partitioning triggers and rules
Date: 2015-03-08 22:44:13
Message-ID: CAGuHJrP7-KB_H3eonGmWpvvi3AwRkgp0X5A4bvv8oPkTtntcsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2015-03-08 22:57:55 Re: Postgres and data warehouses
Previous Message Bill Moran 2015-03-08 16:09:27 Re: Postgres and data warehouses