To: "Steven Flatt" <steven.flatt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Very long SQL strings 
In-reply-to: <357fa7590706211133u2ae43d09id1653ca63d7a7838@mail.gmail.com> 
References: <357fa7590706211133u2ae43d09id1653ca63d7a7838@mail.gmail.com>
Comments: In-reply-to "Steven Flatt" <steven.flatt@gmail.com>
	message dated "Thu, 21 Jun 2007 14:33:01 -0400"
Date: Thu, 21 Jun 2007 14:45:54 -0400
Message-ID: <2239.1182451554@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Steven Flatt" <steven.flatt@gmail.com> writes:
> 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?

Yes, but it'll depend a whole lot on context; I'd suggest
experimentation if you want to derive a number for your particular
situation.  For starters, whether you are on 32- or 64-bit hardware
is hugely relevant.

FYI, when we developed multi-row-VALUES quite a bit of thought was
put into maintaining performance with lots of rows, and IIRC we saw
reasonable performance up into the tens of thousands of rows (depending
on how wide the rows are).  Other ways of making a query long, such as
lots of WHERE clauses, might send performance into the tank a lot
quicker.

So the short answer is it all depends.

			regards, tom lane

PS: for the record, there is a hard limit at 1GB of query text, owing
to restrictions built into palloc.  But I think you'd hit other
memory limits or performance bottlenecks before that one.