From: | Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Issue in Improving the performance using prepared plan |
Date: | 2010-04-22 09:46:53 |
Message-ID: | j2mc11950271004220246u5f694988n3aae14f991f77de3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2010-04-22 09:50:13 | Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !! |
Previous Message | John Gage | 2010-04-22 09:41:22 | Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !! |