pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql7.4.5 running slow on plpgsql function
3+ messages / 2 participants
[nested] [flat]
* Postgresql7.4.5 running slow on plpgsql function
@ 2005-06-23 09:56 Chun Yit(Chronos) <ivanbeh@chronos.com.my>
0 siblings, 1 reply; 3+ messages in thread
From: Chun Yit(Chronos) @ 2005-06-23 09:56 UTC (permalink / raw)
To: pgsql-performance
>hi, need some help with some experts here.
>currently we have a function that use together with temp table, it calls search result function, everytime
>this function is calling, it will go through some filter before come out as a result.
>now we have some major problem , the first time the function execute, it take about 13 second
>second time the function is execute, it take about 17 second, every time you execute the function
>the time taken will grow about 4 second, ?
>may i know what going on here?
>since we use function with temp table, so every statement that related to temp table will using EXECUTE
>command.
>
>regards
>ivan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgresql7.4.5 running slow on plpgsql function
@ 2005-06-28 06:26 Michael Fuhr <mike@fuhr.org>
parent: Chun Yit(Chronos) <ivanbeh@chronos.com.my>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-06-28 06:26 UTC (permalink / raw)
To: Chun Yit(Chronos) <ivanbeh@chronos.com.my>; +Cc: pgsql-performance
On Thu, Jun 23, 2005 at 05:56:52PM +0800, Chun Yit(Chronos) wrote:
>
> currently we have a function that use together with temp table, it calls
> search result function, everytime this function is calling, it will go
> through some filter before come out as a result. now we have some major
> problem , the first time the function execute, it take about 13 second
> second time the function is execute, it take about 17 second, every time
> you execute the function the time taken will grow about 4 second, ? may
> i know what going on here? since we use function with temp table, so
> every statement that related to temp table will using EXECUTE command.
Could you post the function? Without knowing what the code is doing
it's impossible to say what's happening. Is the temporary table
growing on each function call? Does the function delete records
from the table on each call, leaving a lot of dead tuples?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgresql7.4.5 running slow on plpgsql function
@ 2005-06-29 12:50 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-06-29 12:50 UTC (permalink / raw)
To: Chun Yit(Chronos) <ivanbeh@chronos.com.my>; +Cc: pgsql-performance
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]
On Wed, Jun 29, 2005 at 12:29:42PM +0800, Chun Yit(Chronos) wrote:
>
> Yes, the function will delete records from the temporary table every time
> on each call.
> if leaving a lot of dead tuples, then how can we solve it?
If the function deletes all records from the temporary table then
you could use TRUNCATE instead of DELETE. Otherwise you could
VACUUM the table between calls to the function (you can't run VACUUM
inside a function).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-29 12:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 09:56 Postgresql7.4.5 running slow on plpgsql function Chun Yit(Chronos) <ivanbeh@chronos.com.my>
2005-06-28 06:26 ` Michael Fuhr <mike@fuhr.org>
2005-06-29 12:50 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox