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

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

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

[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   = (< 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