pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax length of sql select statement ?
14+ messages / 6 participants
[nested] [flat]
* max length of sql select statement ?
@ 2003-07-07 15:15 markus brosch <brosch [@] gmx [dot] de@mail.bgm-gmbh.de>
2003-07-10 15:24 ` Re: max length of sql select statement ? scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 14+ messages in thread
From: markus brosch @ 2003-07-07 15:15 UTC (permalink / raw)
To: pgsql-sql
Hi All!
I was searching the archive and was wondering why nobody asked this
strange question (or I've not found it?):
"What is the max allowed length of a sql statement or query?"
I want to combine hundrets or thousands 'OR' within a select statement.
Possible or not?
cheers Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:15 max length of sql select statement ? markus brosch <brosch [@] gmx [dot] de@mail.bgm-gmbh.de>
@ 2003-07-10 15:24 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 14+ messages in thread
From: scott.marlowe @ 2003-07-10 15:24 UTC (permalink / raw)
To: markus brosch <"brosch [@] gmx [dot] de"@mail.bgm-gmbh.de>; +Cc: pgsql-sql
On 7 Jul 2003, markus brosch wrote:
> Hi All!
>
> I was searching the archive and was wondering why nobody asked this
> strange question (or I've not found it?):
>
> "What is the max allowed length of a sql statement or query?"
> I want to combine hundrets or thousands 'OR' within a select statement.
> Possible or not?
the maximum length is not limited. i.e.
insert into table badabing values ('10 gig text field goes here');
is legal and will work assuming your machine can handle such a large field
size. i.e. a pentium 100 with 64 meg ram, 64 meg swap and a 500 meg hard
drive can't handle it because of hardware limits, but a Sun E10k with 64
Gigs ram, 64 CPUs, and a 2200 gig raid array likely would.
How many OR statements can be handled is probably limited by some internal
counter or something. More than likely long before the database has
"issues" with too many OR statements, you'll have issues with the
performance (i.e. a non-linear performance curve is likely to exist as you
start swapping out or something like that.)
I'd say experiment and see where postgresql starts to have issues. I'd
certainly like to know too.
I know that dozens or even hundreds of or terms are possible, thousands,
not so sure.
^ permalink raw reply [nested|flat] 14+ messages in thread
* max length of sql select statement ?
@ 2003-07-07 15:44 markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:23 ` Re: max length of sql select statement ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 14+ messages in thread
From: markus brosch @ 2003-07-07 15:44 UTC (permalink / raw)
To: pgsql-sql
Hi All!
I was searching the archive and was wondering why nobody asked this
strange(!) question (or I've not found it?):
"What is the max allowed length of a sql statement or query?"
I want to combine hundrets or thousands 'OR' within a select statement.
Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR
col='c' OR ...... )
This would be a very "dirty" or some would say, a "horrible" solution;
but how are you searching through your table with sql, when your SELECT
is against a collection of different elements (e.g. Array of Strings,
which should fit on one column and return all records which fit)
Hope for help
cheers Markus - sql beginner ;-)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-07 15:57 ` mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
1 sibling, 1 reply; 14+ messages in thread
From: mallah@trade-india.com @ 2003-07-07 15:57 UTC (permalink / raw)
To: brosch@gmx.de; +Cc: pgsql-sql
Depending on ur original problem EXISTS or IN may be usable
EXISTS is efficient and IN can be used efficiently in 7.4 version
of postgresql
regds
mallah.
> Hi All!
>
> I was searching the archive and was wondering why nobody asked this
> strange(!) question (or I've not found it?):
>
> "What is the max allowed length of a sql statement or query?"
> I want to combine hundrets or thousands 'OR' within a select statement.
> Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR
> col='c' OR ...... )
>
> This would be a very "dirty" or some would say, a "horrible" solution;
>
> but how are you searching through your table with sql, when your SELECT
> is against a collection of different elements (e.g. Array of Strings,
> which should fit on one column and return all records which fit)
>
> Hope for help
> cheers Markus - sql beginner ;-)
>
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> commands go to majordomo@postgresql.org
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
@ 2003-07-07 16:07 ` markus brosch <brosch@gmx.de>
2003-07-07 16:13 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:20 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
0 siblings, 2 replies; 14+ messages in thread
From: markus brosch @ 2003-07-07 16:07 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
On Mon, 2003-07-07 at 17:57, mallah@trade-india.com wrote:
> Depending on ur original problem EXISTS or IN may be usable
> EXISTS is efficient and IN can be used efficiently in 7.4 version
> of postgresql
Could be a solution?!
The question is - how long could the IN be?
I mean, if I write something like:
SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
How long can the collection (list) within IN be? Also thousands of
elements?
And what means efficient? Goes the DB only once through the table?
Cheers, Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-07 16:13 ` mallah@trade-india.com
2003-07-07 16:20 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
1 sibling, 1 reply; 14+ messages in thread
From: mallah@trade-india.com @ 2003-07-07 16:13 UTC (permalink / raw)
To: brosch@gmx.de; +Cc: pgsql-sql
> On Mon, 2003-07-07 at 17:57, mallah@trade-india.com wrote:
>> Depending on ur original problem EXISTS or IN may be usable
>> EXISTS is efficient and IN can be used efficiently in 7.4 version of
>> postgresql
>
> Could be a solution?!
> The question is - how long could the IN be?
>
> I mean, if I write something like:
> SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
> How long can the collection (list) within IN be? Also thousands of
> elements?
Well i DO NOT know the exact limit.
May be someone else can answer it accurately.
But you could produce the list within IN using a subselect
that again depends on the exact problem.
regds
Mallah.
>
> And what means efficient? Goes the DB only once through the table?
>
> Cheers, Markus
>
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all lists
> at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:13 ` Re: max length of sql select statement ? mallah@trade-india.com
@ 2003-07-07 16:20 ` markus brosch <brosch@gmx.de>
0 siblings, 0 replies; 14+ messages in thread
From: markus brosch @ 2003-07-07 16:20 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
On Mon, 2003-07-07 at 18:13, mallah@trade-india.com wrote:
> Well i DO NOT know the exact limit.
> May be someone else can answer it accurately.
>
> But you could produce the list within IN using a subselect
> that again depends on the exact problem.
>
Maybe anybody knows how many?
Anyway: My exact problem is "in words" quite easy:
col1 | col2
------------
123 | 958
143 | 394
124 | 345
324 | 345
346 | 541
743 | 144
346 | 986
Imagine, this table is really big (millions records).
Now, I want to retrieve for all records in col A OR col B where either
123, 124, 144, 541 (and a view thousands more ...) fits.
As far as I understud you:
SELECT * FROM table
WHERE col1 IN (123,124,144,541,...)
OR col2 IN (123,124,144,541,...);
Cheers, Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-07 16:20 ` Rod Taylor <rbt@rbt.ca>
2003-07-09 09:45 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
1 sibling, 1 reply; 14+ messages in thread
From: Rod Taylor @ 2003-07-07 16:20 UTC (permalink / raw)
To: markus brosch <brosch@gmx.de>; +Cc: pgsql-sql
> Could be a solution?!
> The question is - how long could the IN be?
I'm not sure about IN specifically, but I know you can do:
SELECT * FROM table WHERE col = '<1GB long file>';
It tends not to be friendly for Ram though :)
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:20 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
@ 2003-07-09 09:45 ` markus brosch <brosch@gmx.de>
2003-07-09 13:29 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 14+ messages in thread
From: markus brosch @ 2003-07-09 09:45 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
On Mon, 2003-07-07 at 18:20, Rod Taylor wrote:
> > Could be a solution?!
> > The question is - how long could the IN be?
>
> I'm not sure about IN specifically, but I know you can do:
> SELECT * FROM table WHERE col = '<1GB long file>';
> It tends not to be friendly for Ram though :)
Hi again!
After I decided to use this statement (discussion two days ago!):
SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
I got this error :-(((
java.sql.SQLException: ERROR: Expression too complex: nesting depth
exceeds max_expr_depth = 10000
I was searching the web and only found this discussion:
http://groups.google.de/groups?hl=de&lr=&ie=UTF-8&oe=UTF-8&threadm=3B2EA1E9.843D940A...
Maybe we can disuss that problem here again?! What exactly means
"max_expr_depth"? Thanks for any help
Cheers Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:20 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
2003-07-09 09:45 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-09 13:29 ` Rod Taylor <rbt@rbt.ca>
2003-07-09 15:51 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
0 siblings, 1 reply; 14+ messages in thread
From: Rod Taylor @ 2003-07-09 13:29 UTC (permalink / raw)
To: markus brosch <brosch@gmx.de>; +Cc: pgsql-sql
> Maybe we can disuss that problem here again?! What exactly means
> "max_expr_depth"? Thanks for any help
If I'm not mistaken, max_expr_depth is used to catch runaway recursion
(view a is select * from b, view b is select * from a).
It's a tunable in postgresql.conf. Toss a couple of 0's behind the
number and uncomment the line.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:20 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
2003-07-09 09:45 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-09 13:29 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
@ 2003-07-09 15:51 ` markus brosch <brosch@gmx.de>
2003-07-09 17:02 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 14+ messages in thread
From: markus brosch @ 2003-07-09 15:51 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
On Wed, 2003-07-09 at 17:45, Rod Taylor wrote:
> > Nobody a better idea? Why is the join of a temporary table (the IN
> > paramters) and the original table so slow? Any tricks here?
>
> Did you index and ANALYZE the temporary table?
No!
I have to do this "strange" and "long" statement also a view thousand
times ... so I always have to create the temp table again and again and
if I index the table it costs me more time.
As far I am relativly new to SQL, I ask you:
Where's the difference between and indexed temp (!!!) table and an
unindexed one? The main table is of course fully indexed ;-)
Cheers, Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` Re: max length of sql select statement ? mallah@trade-india.com
2003-07-07 16:07 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:20 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
2003-07-09 09:45 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-09 13:29 ` Re: max length of sql select statement ? Rod Taylor <rbt@rbt.ca>
2003-07-09 15:51 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-09 17:02 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 14+ messages in thread
From: Rod Taylor @ 2003-07-09 17:02 UTC (permalink / raw)
To: markus brosch <brosch@gmx.de>; +Cc: pgsql-sql
On Wed, 2003-07-09 at 15:51, markus brosch wrote:
> On Wed, 2003-07-09 at 17:45, Rod Taylor wrote:
> > > Nobody a better idea? Why is the join of a temporary table (the IN
> > > paramters) and the original table so slow? Any tricks here?
> >
> > Did you index and ANALYZE the temporary table?
>
> No!
>
> I have to do this "strange" and "long" statement also a view thousand
> times ... so I always have to create the temp table again and again and
> if I index the table it costs me more time.
So.. which costs more. Building the index + fast query or doing the
slow query?
If you have anything more than a thousand lines in the temp table, I bet
indexing it would be the better way to go.
> As far I am relativly new to SQL, I ask you:
> Where's the difference between and indexed temp (!!!) table and an
> unindexed one? The main table is of course fully indexed ;-)
One has an index, which means that other (much faster) methods may be
available for the database to use.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
@ 2003-07-07 16:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-07-07 16:23 ` Re: max length of sql select statement ? markus brosch <brosch@gmx.de>
1 sibling, 1 reply; 14+ messages in thread
From: Stephan Szabo @ 2003-07-07 16:23 UTC (permalink / raw)
To: markus brosch <brosch@gmx.de>; +Cc: pgsql-sql
On 7 Jul 2003, markus brosch wrote:
> I was searching the archive and was wondering why nobody asked this
> strange(!) question (or I've not found it?):
>
> "What is the max allowed length of a sql statement or query?"
AFAIR in recent versions it's effectively limited only by resources (how
much bandwidth/memory do you want to use).
> I want to combine hundrets or thousands 'OR' within a select statement.
> Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR
> col='c' OR ...... )
It should be possible.
> This would be a very "dirty" or some would say, a "horrible" solution;
>
> but how are you searching through your table with sql, when your SELECT
> is against a collection of different elements (e.g. Array of Strings,
> which should fit on one column and return all records which fit)
Are the 'a', 'b', etc... fixed or generated on the fly? I'm not sure
that the plan for a thousand OR clauses (or equivalently a thousand
entries in a static IN) is going to necessarily be terribly good. You
might have better luck setting it up to do a join with a table (possibly a
temporary table if they're generated on the fly).
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: max length of sql select statement ?
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 16:23 ` Re: max length of sql select statement ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-07-07 16:23 ` markus brosch <brosch@gmx.de>
0 siblings, 0 replies; 14+ messages in thread
From: markus brosch @ 2003-07-07 16:23 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
> > but how are you searching through your table with sql, when your SELECT
> > is against a collection of different elements (e.g. Array of Strings,
> > which should fit on one column and return all records which fit)
>
> Are the 'a', 'b', etc... fixed or generated on the fly? I'm not sure
> that the plan for a thousand OR clauses (or equivalently a thousand
> entries in a static IN) is going to necessarily be terribly good. You
> might have better luck setting it up to do a join with a table (possibly a
> temporary table if they're generated on the fly).
Generated "on the fly" by JDBC within Java ;-)
I already tried the join, but it takes ages *g*
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-07-10 15:24 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-07 15:15 max length of sql select statement ? markus brosch <brosch [@] gmx [dot] de@mail.bgm-gmbh.de>
2003-07-10 15:24 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-07 15:44 max length of sql select statement ? markus brosch <brosch@gmx.de>
2003-07-07 15:57 ` mallah@trade-india.com
2003-07-07 16:07 ` markus brosch <brosch@gmx.de>
2003-07-07 16:13 ` mallah@trade-india.com
2003-07-07 16:20 ` markus brosch <brosch@gmx.de>
2003-07-07 16:20 ` Rod Taylor <rbt@rbt.ca>
2003-07-09 09:45 ` markus brosch <brosch@gmx.de>
2003-07-09 13:29 ` Rod Taylor <rbt@rbt.ca>
2003-07-09 15:51 ` markus brosch <brosch@gmx.de>
2003-07-09 17:02 ` Rod Taylor <rbt@rbt.ca>
2003-07-07 16:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-07-07 16:23 ` markus brosch <brosch@gmx.de>
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