On 3/12/15 8:15 AM, Tomas Vondra wrote:
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.
BTW, if you're that concerned about performance you could probably do a
lot better than a plpgsql trigger by creating one in C. There's an
enormous amount of code involved just in parsing and starting a plpgsql
trigger, and then it's going to have to re-parse the dynamic SQL for
every single row, whereas a C trigger could avoid almost all of that.
Rules are likely to be even faster (at least until you get to a fairly
large number of partitions), but as Thomas mentioned they're very tricky
to use. The critical thing to remember with them is they're essentially
hacking on the original query itself.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
