Issue in Improving the performance using prepared plan

[Jignesh Shah <jignesh.shah1980@xxxxxxxxx>]

Hi,

 

I have written following trigger and trying to improve the performance by using prepared query everytime. I have used spi_prepare to prepare the query and $_SHARED global hash to persist the prepared plan but it doesn't seem to work. Though $query will be same always in following trigger, it prepares query everytime and never uses prepared plan.

Could anyone tell me what's wrong going on? 

CREATE OR REPLACE FUNCTION techdb_table_trigger()

  RETURNS trigger AS

$BODY$

our ($id, $query, $plan, $change_log_table);

 

$change_log_table = "ChangeLogTable";

 

$id = $_TD->{new}{'id'};

 

$query   = (<<ENDQUERY); 

    INSERT INTO $change_log_table(id)

    SELECT \$1

    EXCEPT SELECT id

    FROM $change_log_table

    WHERE id = \$1

    AND txid = txid_current()

    AND txtime = transaction_timestamp(); 

ENDQUERY

 

if (exists($_SHARED{$query})) {

   $plan  = $_SHARED{$query};

   elog(INFO, "########## Preparing the query ###########");  --> Always comes here. Don't know why?

} else {

   $plan  = spi_prepare($query, 'INTEGER');

   $_SHARED{$query} = $plan; 

   elog(INFO, "###############Using already prepared the query##############");  --> Never comes here.

}

 

spi_exec_prepared($plan, $id);

 

$BODY$

  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER 

Thanks,

Jignesh