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

Re: Function call with offset and limit

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Function call with offset and limit
Date: 2005-12-22 21:39:58
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
On Thu, Dec 22, 2005 at 11:18:22AM +0100, Martijn van Oosterhout wrote:
> On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote:
> > Hi
> > 
> > I've just tried it, and it works. So it's a good work-around.
> > 
> > Though, is it a wanted feature to have a function being performed on
> > each row before the offset ?
> Well, saying offset 5000 pretty much means to calculate the first 5000
> rows and throw away the result. To calculate that it needs to execute
> the function each time. What happens if the function has side-effects
> like in your case? What if you had a WHERE clause that depended on the
> result of that function?
> If the function has no side-effects, like say pow() then the backend
> could skip but that should be transparent to the user. SQL allows you
> specify the way you want it and PostgreSQL is simply executing what you
> wrote down...

Well, it would be a good optimization to make if the function is
immutable and isn't otherwise referenced (ie: by WHERE or ORDER BY),
there's no reason I can think of to execute it as you read through the
rows. Might be able to do this with STABLE functions as well.

Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software    work: 512-231-6117
vcard:       cell: 512-569-9461

In response to

pgsql-hackers by date

Next:From: Manfred KoizarDate: 2005-12-22 21:58:31
Subject: Re: Re: Which qsort is used
Previous:From: Lukas Kahwe SmithDate: 2005-12-22 21:28:06
Subject: Re: Automatic function replanning

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