Postgres Pro
Downloads
Home   >   mailing lists

Benchmarking partitioning triggers and rules - Mailing list pgsql-general

From Tim Uckun
Subject Benchmarking partitioning triggers and rules
Date
Msg-id CAGuHJrP7-KB_H3eonGmWpvvi3AwRkgp0X5A4bvv8oPkTtntcsw@mail.gmail.com
Whole thread Raw
Responses Re: Benchmarking partitioning triggers and rules
List pgsql-general
Tree view
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.


pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: Postgres and data warehouses
Next
From: John R Pierce
Date:
Subject: Re: Postgres and data warehouses