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: 20051222213958.GN72143@pervasive.com (view raw or flat)
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.

TODO?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       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-2014 The PostgreSQL Global Development Group