Re: Very long SQL strings - Mailing list pgsql-performance

From Tom Lane
Subject Re: Very long SQL strings
Date
Msg-id 2239.1182451554@sss.pgh.pa.us
Whole thread Raw
In response to Very long SQL strings  ("Steven Flatt" <steven.flatt@gmail.com>)
List pgsql-performance
"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.

pgsql-performance by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: Very long SQL strings
Next
From: Andreas Kretschmer
Date:
Subject: Re: Very long SQL strings