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 \$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
-----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:
Show quoted text
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160I have written following trigger and trying to improve the performance by
using prepared query everytime. I have used spi_prepare to prepare thequery
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-------
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general