Issue in Improving the performance using prepared plan

Started by Jignesh Shahalmost 16 years ago3 messagesgeneral
Jump to latest
#1Jignesh Shah
jignesh.shah1980@gmail.com

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

#2Greg Sabino Mullane
greg@turnstep.com
In reply to: Jignesh Shah (#1)
Re: Issue in Improving the performance using prepared plan

-----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-----

#3Jignesh Shah
jignesh.shah1980@gmail.com
In reply to: Greg Sabino Mullane (#2)
Re: Issue in Improving the performance using prepared plan

:) 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: 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-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general