X-Original-To: pgsql-patches-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 6A9F29FA430 for ; Wed, 19 Jul 2006 01:20:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 75031-03 for ; Wed, 19 Jul 2006 04:20:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from joeconway.com (wsip-68-15-9-201.sd.sd.cox.net [68.15.9.201]) by postgresql.org (Postfix) with ESMTP id 4C18F9FA434 for ; Wed, 19 Jul 2006 01:20:07 -0300 (ADT) Received: from [192.168.4.40] (account jconway [192.168.4.40] verified) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 4055093; Tue, 18 Jul 2006 21:20:06 -0700 Message-ID: <44BDB2F5.7080003@joeconway.com> Date: Tue, 18 Jul 2006 21:20:05 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.7.13) Gecko/20060501 Fedora/1.7.13-1.1.fc5 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane CC: Christopher Kings-Lynne , "Patches (PostgreSQL)" , Andrew Dunstan , Bernd Helmle , Peter Eisentraut , "Patches (PostgreSQL)" , Susanne Ebrecht Subject: Re: [HACKERS] 8.2 features? References: <44B69778.5050104@dunslane.net> <50461.84.63.113.55.1152832991.squirrel@pezone.net> <44B7ADFC.90205@dunslane.net> <44BC0432.3040509@joeconway.com> <44BC661F.80609@joeconway.com> <44BCD06C.3060409@calorieking.com> <44BCF1D4.3010807@dunslane.net> <5842.1153246741@sss.pgh.pa.us> <44BD8854.9060506@calorieking.com> <15212.1153279954@sss.pgh.pa.us> In-Reply-To: <15212.1153279954@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------050705080904010101010900" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/150 X-Sequence-Number: 20304 This is a multi-part message in MIME format. --------------050705080904010101010900 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > Christopher Kings-Lynne 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 --------------050705080904010101010900 Content-Type: text/x-patch; name="multi-insert-r2.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="multi-insert-r2.diff" Index: src/backend/parser/analyze.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/parser/analyze.c,v retrieving revision 1.340 diff -c -r1.340 analyze.c *** src/backend/parser/analyze.c 14 Jul 2006 14:52:21 -0000 1.340 --- src/backend/parser/analyze.c 19 Jul 2006 03:53:35 -0000 *************** *** 657,667 **** } else { /* * For INSERT ... VALUES, transform the given list of values to form a ! * targetlist for the INSERT. */ ! qry->targetList = transformTargetList(pstate, stmt->targetList); } /* --- 657,699 ---- } else { + ListCell *tlr; + /* * For INSERT ... VALUES, transform the given list of values to form a ! * targetlist for the INSERT. In a multi-targetlist INSERT, append all ! * but the first targetlist to extras_after to be processed later by ! * do_parse_analyze */ ! qry->targetList = NIL; ! foreach(tlr, stmt->targetList) ! { ! List *tgtlist = (List *) lfirst(tlr); ! ! if (qry->targetList == NIL) ! { ! /* transform the first targetlist */ ! qry->targetList = transformTargetList(pstate, tgtlist); ! } ! else ! { ! /* ! * Create an InsertStmt node for each additional targetlist ! * and append to extras_after ! */ ! InsertStmt *insnode = makeNode(InsertStmt); ! ! insnode->cols = NIL; ! insnode->targetList = list_make1(tgtlist); ! insnode->selectStmt = NULL; ! insnode->relation = stmt->relation; ! ! if (*extras_after == NIL) ! *extras_after = list_make1(insnode); ! else ! *extras_after = lappend(*extras_after, insnode); ! } ! } } /* Index: src/backend/parser/gram.y =================================================================== RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.551 diff -c -r2.551 gram.y *** src/backend/parser/gram.y 3 Jul 2006 22:45:39 -0000 2.551 --- src/backend/parser/gram.y 19 Jul 2006 03:53:40 -0000 *************** *** 238,247 **** qualified_name_list any_name any_name_list any_operator expr_list attrs target_list update_target_list insert_column_list ! insert_target_list def_list indirection opt_indirection ! group_clause TriggerFuncArgs select_limit ! opt_select_limit opclass_item_list ! transaction_mode_list_or_empty TableFuncElementList prep_type_clause prep_type_list execute_param_clause using_clause --- 238,247 ---- qualified_name_list any_name any_name_list any_operator expr_list attrs target_list update_target_list insert_column_list ! insert_target_els insert_target_list insert_target_lists ! def_list indirection opt_indirection group_clause ! TriggerFuncArgs select_limit opt_select_limit ! opclass_item_list transaction_mode_list_or_empty TableFuncElementList prep_type_clause prep_type_list execute_param_clause using_clause *************** *** 5349,5359 **** ; insert_rest: ! VALUES '(' insert_target_list ')' { $$ = makeNode(InsertStmt); $$->cols = NIL; ! $$->targetList = $3; $$->selectStmt = NULL; } | DEFAULT VALUES --- 5349,5359 ---- ; insert_rest: ! VALUES insert_target_lists { $$ = makeNode(InsertStmt); $$->cols = NIL; ! $$->targetList = $2; $$->selectStmt = NULL; } | DEFAULT VALUES *************** *** 5370,5380 **** $$->targetList = NIL; $$->selectStmt = $1; } ! | '(' insert_column_list ')' VALUES '(' insert_target_list ')' { $$ = makeNode(InsertStmt); $$->cols = $2; ! $$->targetList = $6; $$->selectStmt = NULL; } | '(' insert_column_list ')' SelectStmt --- 5370,5380 ---- $$->targetList = NIL; $$->selectStmt = $1; } ! | '(' insert_column_list ')' VALUES insert_target_lists { $$ = makeNode(InsertStmt); $$->cols = $2; ! $$->targetList = $5; $$->selectStmt = NULL; } | '(' insert_column_list ')' SelectStmt *************** *** 8189,8197 **** ; insert_target_list: ! insert_target_el { $$ = list_make1($1); } ! | insert_target_list ',' insert_target_el { $$ = lappend($1, $3); } ; insert_target_el: --- 8189,8215 ---- ; + insert_target_lists: + insert_target_list + { + $$ = list_make1($1); + } + | insert_target_lists ',' insert_target_list + { + $$ = lappend($1, $3); + } + ; + insert_target_list: ! '(' insert_target_els ')' ! { ! $$ = $2; ! } ! ; ! ! insert_target_els: ! insert_target_el { $$ = list_make1($1); } ! | insert_target_els ',' insert_target_el { $$ = lappend($1, $3); } ; insert_target_el: --------------050705080904010101010900 Content-Type: application/x-php; name="test-insert.php" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="test-insert.php" PD9QSFANCi8qDQpjcmVhdGUgdGFibGUgZm9vMmEoZjEgaW50LGYyIGludCk7DQpjcmVhdGUg dGFibGUgZm9vMmIoZjEgaW50LGYyIGludCk7DQpjcmVhdGUgdGFibGUgZm9vMmMoZjEgaW50 LGYyIGludCk7DQoNCiRsb29wY291bnQgPSAxMDAwMDA7DQpzaW5nbGUtSU5TRVJULW11bHRp LXN0YXRlbWVudCBFbGFwc2VkIHRpbWUgaXMgMzQgc2Vjb25kcw0Kc2luZ2xlLUlOU0VSVC1h dC1vbmNlIEVsYXBzZWQgdGltZSBpcyA3IHNlY29uZHMNCm11bHRpLUlOU0VSVC1hdC1vbmNl IEVsYXBzZWQgdGltZSBpcyA0IHNlY29uZHMNCmFib3V0IDM3ME1CIHBlYWsNCg0KJGxvb3Bj b3VudCA9IDIwMDAwMDsNCnNpbmdsZS1JTlNFUlQtbXVsdGktc3RhdGVtZW50IEVsYXBzZWQg dGltZSBpcyA2NyBzZWNvbmRzDQpzaW5nbGUtSU5TRVJULWF0LW9uY2UgRWxhcHNlZCB0aW1l IGlzIDEyIHNlY29uZHMNCm11bHRpLUlOU0VSVC1hdC1vbmNlIEVsYXBzZWQgdGltZSBpcyA5 IHNlY29uZHMNCmFib3V0IDc1ME1CIHBlYWsNCg0KJGxvb3Bjb3VudCA9IDMwMDAwMDsNCnNp bmdsZS1JTlNFUlQtbXVsdGktc3RhdGVtZW50IEVsYXBzZWQgdGltZSBpcyAxMDEgc2Vjb25k cw0Kc2luZ2xlLUlOU0VSVC1hdC1vbmNlIEVsYXBzZWQgdGltZSBpcyAxOCBzZWNvbmRzDQpt dWx0aS1JTlNFUlQtYXQtb25jZSBFbGFwc2VkIHRpbWUgaXMgMTMgc2Vjb25kcw0KYWJvdXQg MS4xR0IgcGVhaw0KDQoqLw0KCXNldF90aW1lX2xpbWl0KDApOw0KCSRsb29wY291bnQgPSAx MDAwMDA7DQoNCgkkY29ubiA9IHBnX2Nvbm5lY3QoImRibmFtZT1yZWdyZXNzaW9uIHVzZXI9 cG9zdGdyZXMgcG9ydD01NTQzNyIpOw0KCWlmICgkY29ubikNCgkJZWNobyAiQ29ubmVjdCBz dWNjZXNzPGJyPiI7DQoJZWxzZQ0KCQllY2hvICJDb25uZWN0IGZhaWx1cmU8YnI+IjsNCglm bHVzaCgpOw0KDQoJJHNxbCA9ICJJTlNFUlQgSU5UTyBmb28yYSAoZjEsZjIpIFZBTFVFUyAo MSwyKSI7DQoJJHN0YXJ0dGltZSA9IHRpbWUoKTsNCglmb3IgKCRpID0gMDsgJGkgPCAkbG9v cGNvdW50OyAkaSsrKQ0KCQkkcmVzdWx0ID0gcGdfcXVlcnkoJGNvbm4sICRzcWwpOw0KCSRl bmR0aW1lID0gdGltZSgpOw0KCSRlbGFwc2VkID0gKCRlbmR0aW1lIC0gJHN0YXJ0dGltZSk7 DQoJaWYgKCRyZXN1bHQpDQoJCWVjaG8gInNpbmdsZS1JTlNFUlQtbXVsdGktc3RhdGVtZW50 IEVsYXBzZWQgdGltZSBpcyAkZWxhcHNlZCBzZWNvbmRzPEJSPiI7DQoJZWxzZQ0KCQllY2hv IHBnX2xhc3Rfbm90aWNlKCRjb25uKTsNCglmbHVzaCgpOw0KDQoJJHNxbCA9ICIiOw0KCWZv ciAoJGkgPSAwOyAkaSA8ICRsb29wY291bnQ7ICRpKyspDQoJCSRzcWwgLj0gIklOU0VSVCBJ TlRPIGZvbzJiIChmMSxmMikgVkFMVUVTICgxLDIpOyI7DQoJJHN0YXJ0dGltZSA9IHRpbWUo KTsNCgkkcmVzdWx0ID0gcGdfcXVlcnkoJGNvbm4sICRzcWwpOw0KCSRlbmR0aW1lID0gdGlt ZSgpOw0KCSRlbGFwc2VkID0gKCRlbmR0aW1lIC0gJHN0YXJ0dGltZSk7DQoJaWYgKCRyZXN1 bHQpDQoJCWVjaG8gInNpbmdsZS1JTlNFUlQtYXQtb25jZSBFbGFwc2VkIHRpbWUgaXMgJGVs YXBzZWQgc2Vjb25kczxCUj4iOw0KCWVsc2UNCgkJZWNobyBwZ19sYXN0X25vdGljZSgkY29u bik7DQoJZmx1c2goKTsNCg0KCSRzcWwgPSAiSU5TRVJUIElOVE8gZm9vMmMgKGYxLGYyKSBW QUxVRVMgKDEsMikiOw0KCWZvciAoJGkgPSAxOyAkaSA8ICRsb29wY291bnQ7ICRpKyspDQoJ CSRzcWwgLj0gIiwoMSwyKSI7DQoJJHN0YXJ0dGltZSA9IHRpbWUoKTsNCgkkcmVzdWx0ID0g cGdfcXVlcnkoJGNvbm4sICRzcWwpOw0KCSRlbmR0aW1lID0gdGltZSgpOw0KCSRlbGFwc2Vk ID0gKCRlbmR0aW1lIC0gJHN0YXJ0dGltZSk7DQoJaWYgKCRyZXN1bHQpDQoJCWVjaG8gIm11 bHRpLUlOU0VSVC1hdC1vbmNlIEVsYXBzZWQgdGltZSBpcyAkZWxhcHNlZCBzZWNvbmRzPEJS PiI7DQoJZWxzZQ0KCQllY2hvIHBnX2xhc3Rfbm90aWNlKCRjb25uKTsNCglmbHVzaCgpOw0K DQoJcGdfY2xvc2UoJGNvbm4pOw0KPz4NCg== --------------050705080904010101010900--