On Thursday, December 20, 2012, Scott Marlowe wrote:
3: Someone above mentioned rules being faster than triggers. In my
experience they're WAY slower than triggers but maybe that was just on
the older pg versions (8.3 and lower) we were doing this on. I'd be
interested in seeing some benchmarks if rules have gotten faster or I
was just doing it wrong.
It apparently depends on how you use them.
To load 1e6 rows into the parent, redistributing to 100 partitions (rows evenly distributed over partitions) using RULEs, it took 14.5 seconds using a "insert into foo select * from foo_tmp" (not counting the time it took to prepopulate the foo_tmp via \copy).
This is about 25% faster than the 18.4 seconds it took to load the same data via \copy using a plpgsql trigger which was structured with nested IF ... ELSE...END IF that do a binary search over the partitions.
However if I didn't use \copy or "insert into...select", but rather used a Perl loop invoking normal single-row inserts (but all in a single transaction) with DBD::Pg, then the RULEs took 596 seconds, an astonishing seven times slower than the 83 seconds it took the previously mentioned plpgsql trigger to do the same thing.
This was under 9.1.7.
In 9.2.2, it seems to get 3 times worse yet for RULEs in the insert loop. But that result seems hard to believe, so I am repeating it.
Cheers
Jeff