> I tried the following to find out whether a table has any records
> with field1 < X (for a constant X):
> tgl=> SELECT EXISTS(SELECT * FROM table WHERE field1 < X);
> ERROR: internal error: do not know how to transform targetlist
> Is this a bug? (I'm using development sources from yesterday.)
> Am I using EXISTS() incorrectly? The examples I've been able to find
> only show it as a part of a WHERE clause.
> If it did work, would it be any faster than a table scan? The code
> I was hoping to replace is like this:
> SELECT COUNT(field1) WHERE field1 < X;
> // test whether result > 0
> Since aggregates aren't optimized very well, this ends up reading
> much or all of the table, even if there is an index for field1.
> I was hoping EXISTS() might be smarter...
> regards, tom lane
Should have given a syntax error probably. But you might try:
select 1 where exists (select...);
Should be faster if and only if we are doing the existential query
optimization trick (stop on the first qualifying row).
David Gould dg(at)illustra(dot)com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- If simplicity worked, the world would be overrun with insects. -
In response to
pgsql-hackers by date
|Next:||From: Vadim Mikheev||Date: 1998-07-15 02:29:31|
|Subject: Re: [HACKERS] SEQUENCES and COPY FROM|
|Previous:||From: Tom Lane||Date: 1998-07-14 21:31:36|
|Subject: "internal error" triggered by EXISTS()|