| From: | "Cristian Prieto" <cristian(at)clickdiario(dot)com> | 
|---|---|
| To: | <pgsql-hackers(at)postgresql(dot)org> | 
| Subject: | replan needed? | 
| Date: | 2005-12-12 21:18:29 | 
| Message-ID: | 004201c5ff61$9e751130$6500a8c0@gt.ClickDiario.local | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-hackers | 
I have a few SPs I wrote in C language in PostgreSQL 8.1, they do mainly SPI
functions as SELECT field1, field2 from table1 and INSERT INTO table2
VALUES (val1, val2) and to minimize the execution time I do something like
that:
static void *splan_insertstuff = NULL;
PG_FUNCTION_INFO_V1(insertstuff);
Datum insertstuff(PG_FUNCTION_ARGS)
{
	void		*pplan;
	Datum		args[2];
	Oid		argt[2];
	SPI_connect();
	args[0] = PG_GETARG_DATUM(0);
	args[1] = PG_GETARG_DATUM(1);
	if(splan_insertstuff == NULL) {
		pplan = SPI_prepare(
         "INSERT INTO table1 (field1, field2) VALUES ($1, $2), 2, &argt);
		if(pplan == NULL) {
			SPI_finish();
			PG_RETURN_NULL();
		}
		splan_insertstuff = SPI_saveplan(pplan);
	}
	pplan = splan_insertstuff;
	SPI_execute_plan(pplan, &args, NULL, false, 1);
	SPI_finish();
	PG_RETURN_NULL();
}
Well, the table1 is plain easy, just two text fields with an index in the
field1. I have a daemon running to empty table1 and pass the data to another
table. The main question is here: When I delete several tuples from table1
do I need to regenerate the plan? I mean, I store the saved plan in a static
global variable, and the connection stay alive at least for the next 5
hours (I empty the table each 15 min). I have autovacuum running and my
table1 grows around 10000 tuples per minute. I have a performance drop in
the next 2 or 3 hours running the database. There is something related to my
sps here or do I need to search in another place?
Somebody could help me with this?
Thanks a lot!
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2005-12-12 21:19:54 | Anyone for adding -fwrapv to our standard CFLAGS? | 
| Previous Message | Jim C. Nasby | 2005-12-12 20:40:03 | Re: Please Help: PostgreSQL Query Optimizer |