Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id EDDA89FA434 for ; Thu, 21 Jun 2007 15:33:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 34166-01-5 for ; Thu, 21 Jun 2007 15:33:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.230]) by postgresql.org (Postfix) with ESMTP id EF0B79FC152 for ; Thu, 21 Jun 2007 15:33:02 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so550269wxc for ; Thu, 21 Jun 2007 11:33:02 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=dLRE4D25xlGVHW+URb/tbJGwYjwnhYbCAQaRBw6zlb3BM8wD6lYVyPpVC26FV8Of1SiTcQGa3N0YiIUnX7aS7HUMKfjrXwEiYAnEkDqSz+5J3Bda9CqH8NOuoLEPOmUJJBNsQv+sv88ToxhtksadaQ29z2Y79RiPCWmZSh524Sw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=CY7xic+NJlKs1/S9G0DD/hda3w8T5vvI9S1L0lt5vSOTsE9Ja/b2qxSNWhdPtivTYmtzq9WYR+9lyQi5/SyJk9Ti/bb3XTxeCni5yFTm9USHQjptXISsyJsPnGPbOprKfpVxeyu7HrkzCzd6zvWE+ygO5nqXtdXZKVZpWq8F5m8= Received: by 10.70.113.13 with SMTP id l13mr3412594wxc.1182450782016; Thu, 21 Jun 2007 11:33:02 -0700 (PDT) Received: by 10.70.109.14 with HTTP; Thu, 21 Jun 2007 11:33:01 -0700 (PDT) Message-ID: <357fa7590706211133u2ae43d09id1653ca63d7a7838@mail.gmail.com> Date: Thu, 21 Jun 2007 14:33:01 -0400 From: "Steven Flatt" To: pgsql-performance@postgresql.org Subject: Very long SQL strings MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_107885_15528188.1182450781975" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.146 tagged_above=0 required=5 tests=AWL=-1.207, BAYES_50=0.001, HTML_10_20=1.351, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/480 X-Sequence-Number: 25513 ------=_Part_107885_15528188.1182450781975 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I can't seem to find a definitive answer to this. It looks like Postgres does not enforce a limit on the length of an SQL string. Great. However is there some point at which a query string becomes ridiculously too long and affects performance? Here's my particular case: consider an INSERT statement where you're using the new multi-row VALUES clause or SELECT ... UNION ALL to group together tuples. Is it always better to group as many together as possible? For example, on a toy table with two columns, I noticed about a 20% increase when bulking together 1000 tuples in one INSERT statement as opposed to doing 1000 individual INSERTS. Would this be the same for 10000? 100000? Does it depend on the width of the tuples or the data types? Are there any values A and B such that grouping together A tuples and B tuples separately and running two statements, will be faster than grouping A+B tuples in one statement? Steve ------=_Part_107885_15528188.1182450781975 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I can't seem to find a definitive answer to this.
 
It looks like Postgres does not enforce a limit on the length of an SQL string.  Great.  However is there some point at which a query string becomes ridiculously too long and affects performance?  Here's my particular case: consider an INSERT statement where you're using the new multi-row VALUES clause or SELECT ... UNION ALL to group together tuples.  Is it always better to group as many together as possible?
 
For example, on a toy table with two columns, I noticed about a 20% increase when bulking together 1000 tuples in one INSERT statement as opposed to doing 1000 individual INSERTS.  Would this be the same for 10000? 100000?  Does it depend on the width of the tuples or the data types?
 
Are there any values A and B such that grouping together A tuples and B tuples separately and running two statements, will be faster than grouping A+B tuples in one statement?
 
Steve
 
------=_Part_107885_15528188.1182450781975--