Re: Performance on Bulk Insert to Partitioned Table

Hello

2012/12/28 Luciano Ernesto da Silva <luciano(at)cpd(dot)ufrgs(dot)br>:
> UNSUBSCRIBE
>
>
>
> De: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] Em nome de Jeff Janes
> Enviada em: sexta-feira, 28 de dezembro de 2012 14:31
> Para: Scott Marlowe
> Cc: Tom Lane; Charles Gomes; Ondrej Ivanič; pgsql-performance(at)postgresql(dot)org
> Assunto: Re: [PERFORM] 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.
>
>

I am not sure, but I expect so speed or slowness of rules depends
primary on number of partitions. More significantly than triggers.

Regards

Pavel

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