replan needed?
От | Cristian Prieto |
---|---|
Тема | replan needed? |
Дата | |
Msg-id | 004201c5ff61$9e751130$6500a8c0@gt.ClickDiario.local обсуждение исходный текст |
Список | 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!
В списке pgsql-hackers по дате отправления: