[GENERAL] Issue in Improving the performance using prepared plan

[Jignesh Shah]

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