[HACKERS] replan needed?

[Cristian Prieto]

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!

 



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend