Обсуждение: Issue in Improving the performance using prepared plan
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 \$1EXCEPT SELECT idFROM $change_log_tableWHERE id = \$1AND txid = txid_current()AND txtime = transaction_timestamp();ENDQUERYif (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
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 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? Works fine for me. Note that your elog outputs are switched - you are claiming the already prepared plan for the first time (if exists) and claiming the first prepare when in fact it is reusing (else). - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004220922 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkvQTasACgkQvJuQZxSWSsiH1wCgwiuBRmjmGZ0WWKKD/6BwovhR M7IAoME88RAuNAd0P1tH4ug/I8FFJ8Bj =CG70 -----END PGP SIGNATURE-----
:) I realized that. Thanks.
On Thu, Apr 22, 2010 at 6:53 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160Works fine for me. Note that your elog outputs are switched - you are claiming
> 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?
the already prepared plan for the first time (if exists) and claiming the
first prepare when in fact it is reusing (else).
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004220922
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkvQTasACgkQvJuQZxSWSsiH1wCgwiuBRmjmGZ0WWKKD/6BwovhR
M7IAoME88RAuNAd0P1tH4ug/I8FFJ8Bj
=CG70
-----END PGP SIGNATURE-----
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general