Re: [GENERAL] Issue in Improving the performance using prepared plan

2010-04-23 Thread Jignesh Shah 
:) I realized that. Thanks.

On Thu, Apr 22, 2010 at 6:53 PM, Greg Sabino Mullane g...@turnstep.comwrote:


 -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 g...@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

[GENERAL] Issue in Improving the performance using prepared plan

2010-04-22 Thread 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

Re: [GENERAL] Issue in Improving the performance using prepared plan

2010-04-22 Thread Greg Sabino Mullane 

-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 g...@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