Are you going to apply this? Seems it is ready. ---------------------------------------------------------------------------
Joe Conway wrote: > Tom Lane wrote: > > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > > >>Strange. Last time I checked I thought MySQL dump used 'multivalue > >>lists in inserts' for dumps, for the same reason that we use COPY > > > > I think Andrew identified the critical point upthread: they don't try > > to put an unlimited number of rows into one INSERT, only a megabyte > > or so's worth. Typical klugy-but-effective mysql design approach ... > > > OK, so given that we don't need to be able to do 1 million > multi-targetlist insert statements, here is rev 2 of the patch. > > It is just slightly more invasive, but performs *much* better. In fact, > it can handle as many targetlists as you have memory to deal with. It > also deals with DEFAULT values in the targetlist. > > I've attached a php script that I used to do crude testing. Basically I > tested 3 cases in this order: > > single-INSERT-multi-statement: > ------------------------------ > "INSERT INTO foo2a (f1,f2) VALUES (1,2);" > -- repeat statement $loopcount times > > single-INSERT-at-once: > ---------------------- > "INSERT INTO foo2b (f1,f2) VALUES (1,2);INSERT INTO foo2a (f1,f2) > VALUES (1,2);INSERT INTO foo2a (f1,f2) VALUES (1,2)..." > -- build a single SQL string by looping $loopcount times, > -- and execute it all at once > > multi-INSERT-at-once: > --------------------- > "INSERT INTO foo2c (f1,f2) VALUES (1,2),(1,2),(1,2)..." > -- build a single SQL string by looping $loopcount times, > -- and execute it all at once > > Here are the results: > $loopcount = 100000; > single-INSERT-multi-statement Elapsed time is 34 seconds > single-INSERT-at-once Elapsed time is 7 seconds > multi-INSERT-at-once Elapsed time is 4 seconds > about 370MB peak memory usage > > $loopcount = 200000; > single-INSERT-multi-statement Elapsed time is 67 seconds > single-INSERT-at-once Elapsed time is 12 seconds > multi-INSERT-at-once Elapsed time is 9 seconds > about 750MB peak memory usage > > $loopcount = 300000; > single-INSERT-multi-statement Elapsed time is 101 seconds > single-INSERT-at-once Elapsed time is 18 seconds > multi-INSERT-at-once Elapsed time is 13 seconds > about 1.1GB peak memory usage > > Somewhere beyond this, my machine goes into swap hell, and I didn't have > the patience to wait for it to complete :-) > > It would be interesting to see a side-by-side comparison with MySQL > since that seems to be our benchmark on this feature. I'll try to do > that tomorrow if no one beats me to it. > > There is only one downside to the current approach that I'm aware of. > The command-result tag is only set by the "original" query, meaning that > even if you insert 300,000 rows using this method, the command-result > tag looks like "INSERT 0 1"; e.g.: > > regression=# create table foo2(f1 int default 42,f2 int default 6); > CREATE TABLE > regression=# insert into foo2 (f1,f2) values > (default,12),(default,10),(115,21); > INSERT 0 1 > regression=# select * from foo2; > f1 | f2 > -----+---- > 42 | 12 > 42 | 10 > 115 | 21 > (3 rows) > > Any thoughts on how to fix that? > > Thanks, > > Joe > > [ application/x-php is not supported, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
