Thread: Using LIMIT 1 in plpgsql PERFORM statements
Hi,
I'm wondering if the plpgsql code:
PERFORM 1 FROM foo;
IF FOUND THEN ...
is any slower than:
PERFORM 1 FROM foo LIMIT 1;
IF FOUND THEN ...
Seems like it _could_ be smart enough to know that
1) It's selecting from a real table and not a function
2) GET DIAGNOSTICS is not used
and therefore it does not have to do more than set
FOUND, and need find only one row/plan the query
to find only one row. I'm particularly interested
in the query plan optimization aspect.
Would it be considered poor practice to rely on
such an optimization?
Thanks.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Karl, > PERFORM 1 FROM foo; > IF FOUND THEN ... > > is any slower than: > > PERFORM 1 FROM foo LIMIT 1; > IF FOUND THEN ... I'm wondering in what context it makes sense to call PERFORM on a constant. -- Josh Berkus Aglio Database Solutions San Francisco
On 10/23/2005 04:02:35 PM, Josh Berkus wrote:
> Karl,
>
> > PERFORM 1 FROM foo;
> > IF FOUND THEN ...
> >
> > is any slower than:
> >
> > PERFORM 1 FROM foo LIMIT 1;
> > IF FOUND THEN ...
>
> I'm wondering in what context it makes sense to call PERFORM on a
> constant.
If you want to find out if the table has any rows.
I'm really interested in what happens when
there's a WHERE qualification. I want to find
out if there's any of some particular sort of row.
But I figured it wasn't worth putting that into
the example because I didn't have anything
specific to put in the WHERE clause. I suppose
I should have put it in anyway and followed with ....
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
On 10/23/2005 04:02:35 PM, Josh Berkus wrote:
> I'm wondering in what context it makes sense to call PERFORM on a
> constant.
I like to write PERFORMs that return a constant when
selecting from a table. It emphasizes that the
selection is being done for its side effects.
(Programs should be written for people to read
and only incidentally for computers to execute.
Programs that people can't read quickly
become useless whereas programs that can't run
quickly can be fixed. Computers are easy.
People are difficult.)
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Karl, > I like to write PERFORMs that return a constant when > selecting from a table. It emphasizes that the > selection is being done for its side effects. Well, there's always the destruction test: run each version of the function 10,000 times and see if there's an execution time difference. > (Programs should be written for people to read > and only incidentally for computers to execute. > Programs that people can't read quickly > become useless whereas programs that can't run > quickly can be fixed. Computers are easy. > People are difficult.) That's a nice sentiment, but I don't see how it applies. For example, if I do: SELECT id INTO v_check FROM some_table ORDER BY id LIMIT 1; IF id > 0 THEN .... ... that says pretty clearly to code maintainers that I'm only interested in finding out whether there's any rows in the table, while making sure I use the index on ID. If I want to make it more clear, I do: -- check whether the table is populated Not that there's anything wrong with your IF FOUND approach, but let's not mix up optimizations and making your code pretty ... especially for a SQL scripting language. -- Josh Berkus Aglio Database Solutions San Francisco
On Sun, 2005-23-10 at 21:36 -0700, Josh Berkus wrote: > SELECT id INTO v_check > FROM some_table ORDER BY id LIMIT 1; > > IF id > 0 THEN .... > > ... that says pretty clearly to code maintainers that I'm only interested in > finding out whether there's any rows in the table, while making sure I use > the index on ID. Why would you want to use the index on ID? -Neil