Обсуждение: Function call with offset and limit
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);setval
-------- 1
(1 row)
test=# select currval('test_truc') ;currval
--------- 1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;idkeyword | test_func
-----------+------------- 5001 | 5002
(1 row)
test=# select currval('test_truc') ;currval
--------- 5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel <reynaud@elma.fr>
Elma
Have you tried
SELECT *, test_func(idkeyword) FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;
?
This should probably have been on -general, btw.
On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
> Hi all,
>
> We need to find a solution for a strange problem.
> We have a plpgsql FUNCTION which performs an heavy job (named
> test_func).
>
> CREATE or replace function test_func(z int) returns integer as $$
> declare
> tst integer;
> begin
> --
> -- Large jobs with z
> --
> tst := nextval('test_truc');
> return tst;
> end;
> $$ LANGUAGE plpgsql;
>
>
> So I made this test:
>
> test=# select setval('test_truc',1);
> setval
> --------
> 1
> (1 row)
>
> test=# select currval('test_truc') ;
> currval
> ---------
> 1
> (1 row)
>
> test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
> idkeyword | test_func
> -----------+-------------
> 5001 | 5002
> (1 row)
>
> test=# select currval('test_truc') ;
> currval
> ---------
> 5002
> (1 row)
>
>
> This demonstrates that the function is called 5001 times though only one
> row is returned. Problem is that this heavy job is performed much, much
> more than needed.
>
> But, If I do:
> test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
> My function is called only once.
>
> Is there any work around ?
>
>
> Thanks
> --
> REYNAUD Jean-Samuel <reynaud@elma.fr>
> Elma
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 ?
Le mercredi 21 décembre 2005 à 13:41 -0600, Jim C. Nasby a écrit :
> Have you tried
>
> SELECT *, test_func(idkeyword)
> FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
> ;
>
> ?
>
> This should probably have been on -general, btw.
>
> On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
> > Hi all,
> >
> > We need to find a solution for a strange problem.
> > We have a plpgsql FUNCTION which performs an heavy job (named
> > test_func).
> >
> > CREATE or replace function test_func(z int) returns integer as $$
> > declare
> > tst integer;
> > begin
> > --
> > -- Large jobs with z
> > --
> > tst := nextval('test_truc');
> > return tst;
> > end;
> > $$ LANGUAGE plpgsql;
> >
> >
> > So I made this test:
> >
> > test=# select setval('test_truc',1);
> > setval
> > --------
> > 1
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 1
> > (1 row)
> >
> > test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
> > idkeyword | test_func
> > -----------+-------------
> > 5001 | 5002
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 5002
> > (1 row)
> >
> >
> > This demonstrates that the function is called 5001 times though only one
> > row is returned. Problem is that this heavy job is performed much, much
> > more than needed.
> >
> > But, If I do:
> > test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
> > My function is called only once.
> >
> > Is there any work around ?
> >
> >
> > Thanks
> > --
> > REYNAUD Jean-Samuel <reynaud@elma.fr>
> > Elma
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
--
REYNAUD Jean-Samuel <reynaud@elma.fr>
Elma
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... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461