Hello 2012/12/28 Luciano Ernesto da Silva <luciano@xxxxxxxxxxxx>: > UNSUBSCRIBE > > > > De: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] 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@xxxxxxxxxxxxxx > Assunto: 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. > > 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 > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
