functions and plans

Started by Sim Zacksover 20 years ago4 messagesgeneral
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sim Zacks (#2)
Re: functions and plans

Sim Zacks <sim@compulab.co.il> writes:

I have a table that I populate, select and delete the data each time the
function is run. Each time I run the function it takes longer to get the
data. In these tests, the data is the exact same every time.

I tried putting vacuum inside the function, but I got an error, cannot
run vacuum inside of function.

TRUNCATE, maybe?

regards, tom lane

#2Sim Zacks
sim@compulab.co.il

I am trying to figure out some issues with my functions and I was hoping
someone could help me.
My understanding is that the plan is made when the function is run the
first time. That means that running analyze on a table after a function
is run once won't change the plan.

I have a table that I populate, select and delete the data each time the
function is run. Each time I run the function it takes longer to get the
data. In these tests, the data is the exact same every time.

I tried putting vacuum inside the function, but I got an error, cannot
run vacuum inside of function.

Any thoughts?

#3Sim Zacks
sim@compulab.co.il
In reply to: Tom Lane (#1)
Re: functions and plans

Truncate won't work for me because I don't completely empty the table, I
just empty it for that user.
Each user has the table populated with his data, basically to work like
a temporary table. When the user runs a function it repopulates his
working table and then until he runs the function again he has that
dataset to work with. (I just noticed I wrote select and delete,
actually it deletes and then selects)

Tom Lane wrote:

Show quoted text

Sim Zacks <sim@compulab.co.il> writes:

I have a table that I populate, select and delete the data each time the
function is run. Each time I run the function it takes longer to get the
data. In these tests, the data is the exact same every time.

I tried putting vacuum inside the function, but I got an error, cannot
run vacuum inside of function.

TRUNCATE, maybe?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#4Jim Nasby
jnasby@pervasive.com
In reply to: Sim Zacks (#2)
Re: functions and plans

On Tue, May 23, 2006 at 04:19:56PM +0200, Sim Zacks wrote:

I am trying to figure out some issues with my functions and I was hoping
someone could help me.
My understanding is that the plan is made when the function is run the
first time. That means that running analyze on a table after a function
is run once won't change the plan.

I have a table that I populate, select and delete the data each time the
function is run. Each time I run the function it takes longer to get the
data. In these tests, the data is the exact same every time.

I tried putting vacuum inside the function, but I got an error, cannot
run vacuum inside of function.

Any thoughts?

Best bet would probably be to turn on autovacuum. Short of that, setup a
cronjob.
--
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