From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long SQL strings
Date: Thu, 21 Jun 2007 14:45:54 -0400
Message-ID: <2239.1182451554@sss.pgh.pa.us> (raw)
In-Reply-To: <357fa7590706211133u2ae43d09id1653ca63d7a7838@mail.gmail.com>
References: <357fa7590706211133u2ae43d09id1653ca63d7a7838@mail.gmail.com>
"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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, steven.flatt@gmail.com
Subject: Re: Very long SQL strings
In-Reply-To: <2239.1182451554@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox