Skip site navigation (1) Skip section navigation (2)

Re: Very long SQL strings

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long SQL strings
Date: 2007-06-21 18:45:54
Message-ID: 2239.1182451554@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Steven Flatt" <steven(dot)flatt(at)gmail(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Andreas KretschmerDate: 2007-06-21 18:48:33
Subject: Re: Very long SQL strings
Previous:From: Heikki LinnakangasDate: 2007-06-21 18:41:55
Subject: Re: Very long SQL strings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group