Re: Performance on Bulk Insert to Partitioned Table

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

>
>