Benchmarking partitioning triggers and rules

Поиск
Список
Период
Сортировка
От Tim Uckun
Тема Benchmarking partitioning triggers and rules
Дата
Msg-id CAGuHJrP7-KB_H3eonGmWpvvi3AwRkgp0X5A4bvv8oPkTtntcsw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Benchmarking partitioning triggers and rules
Список 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.


В списке pgsql-general по дате отправления:

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.