Re: Benchmarking partitioning triggers and rules

On 12.3.2015 04:57, Tim Uckun wrote:
> 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!

I have no idea what brew or postgres.app is. But I strongly recommend
you to do some tuning.

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

>
> 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.

Because processing dynamic SQL commands (i.e. EXECUTE '...') is simply
more expensive, as it needs to do more stuff (on every execution). There
are reasons for that, but you may think of it as regular queries vs.
prepared statements.

Prepared statements are parsed and planned once, regular query needs to
be parsed and planned over and over again.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services