Le 11/10/2017 à 16:11, Purav Chovatia a écrit :
Thanks.
We looked at pg_stat_statements and we see execution count & total
time taken. But that still does not help me to identify why is it slow
or what is taking time or where is the wait.
btw, does pg_stat_statements add considerable overhe
2017-10-11 18:52 GMT+02:00 Purav Chovatia :
> Yes, there is some code to catch exceptions like unique constraint
> violation and no data found. Do you suggest we trying by commenting that
> part? btw, the dataset is a controlled one, so what I can confirm is we are
> not hitting any exceptions.
>
> Yes, there is some code to catch exceptions like unique constraint violation
> and no data found. Do you suggest we trying by commenting that part?
That is likely it. Comment that out and test.
If you still need to handle a unique violation, see if you can instead
use the ON CONFLICT clause on
Yes, there is some code to catch exceptions like unique constraint
violation and no data found. Do you suggest we trying by commenting that
part? btw, the dataset is a controlled one, so what I can confirm is we are
not hitting any exceptions.
Thanks
On 11 October 2017 at 22:07, Adam Brusselback
Is there any error handling in there? I remember seeing performance
issues if you put in any code to catch exceptions.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Thanks Pavel. Our SPs are not doing any mathematical calculations. Its
mostly if-else, so I would expect good performance.
On 11 October 2017 at 19:50, Pavel Stehule wrote:
>
>
> 2017-10-11 15:59 GMT+02:00 Purav Chovatia :
>
>> Thanks Laurenz, am having a look at perf.
>>
>> Can you pls help und
2017-10-11 15:59 GMT+02:00 Purav Chovatia :
> Thanks Laurenz, am having a look at perf.
>
> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
> is not optimized for performance like PL/SQL". Do you mean to indicate that
> app firing queries/DMLs directly would be a better
Thanks.
We looked at pg_stat_statements and we see execution count & total time
taken. But that still does not help me to identify why is it slow or what
is taking time or where is the wait.
btw, does pg_stat_statements add considerable overhead? Coming from the
Oracle world, we are very used to
Thanks Laurenz, am having a look at perf.
Can you pls help understand what exactly do you mean when you say "PL/pgSQL
is not optimized for performance like PL/SQL". Do you mean to indicate that
app firing queries/DMLs directly would be a better option as compared to
putting those in Stored Procs?
2017-10-03 17:17 GMT+02:00 Adam Brusselback :
> There is also the option of pg_stat_statements: https://
> www.postgresql.org/docs/current/static/pgstatstatements.html and
> auto_explain: https://www.postgresql.org/docs/current/
> static/auto-explain.html
>
> These should help you identify what is
There is also the option of pg_stat_statements:
https://www.postgresql.org/docs/current/static/pgstatstatements.html and
auto_explain:
https://www.postgresql.org/docs/current/static/auto-explain.html
These should help you identify what is slowing things down. There is no
reason I could think of y
Purav Chovatia wrote:
> I come from Oracle world and we are porting all our applications to
> postgresql.
>
> The application calls 2 stored procs,
> - first one does a few selects and then an insert
> - second one does an update
>
> The main table on which the insert and the update happens is
Hello,
I come from Oracle world and we are porting all our applications to
postgresql.
The application calls 2 stored procs,
- first one does a few selects and then an insert
- second one does an update
The main table on which the insert and the update happens is truncated
before every performan
piuschan writes:
> PostgreSQL: 9.1.11
> Since July 1, one SP suddenly runs slowly in HOT STANDBY server. After
> investigation, I can narrow the problem to one particular query in SP.
>SELECT MIN(locate_id) INTO v_min_locate_id
>FROM event_startstop
>WHERE beacon_id = p_beacon_id
We have two database servers running streaming replication between them:
Primary server
==
OS: Linux version 2.6.18-371.3.1.el5
PostgreSQL: 9.1.11
HOT standby server
==
OS: Linux version 2.6.32-431.11.2.el6.x86_64
PostgreSQL: 9.1.11
Since July 1, one SP suddenly runs
Thank you for all of the responses. This was really helpful.
Damon
On Sat, Oct 16, 2010 at 12:54 PM, Merlin Moncure wrote:
> On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane wrote:
> > Tatsuo Ishii writes:
> >> So can I say "if a function is marked IMMUTABLE, then it should never
> >> modify databa
On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane wrote:
> Tatsuo Ishii writes:
>> So can I say "if a function is marked IMMUTABLE, then it should never
>> modify database"? Is there any counter example?
>> It seems if above is correct, I can say STABLE functions should never
>> modify databases as well
Tatsuo Ishii writes:
> So can I say "if a function is marked IMMUTABLE, then it should never
> modify database"? Is there any counter example?
> It seems if above is correct, I can say STABLE functions should never
> modify databases as well.
Both of those things are explicitly stated here:
http:
> broadly speaking:
> *) function generates same output from inputs regardless of what's
> going on in the database, and has no side effects: IMMUTABLE
So can I say "if a function is marked IMMUTABLE, then it should never
modify database"? Is there any counter example?
> *) function reads (only)
On Mon, Oct 11, 2010 at 7:10 PM, Damon Snyder wrote:
> Hello,
> I have heard it said that if a stored procedure is declared as VOLATILE,
> then no good optimizations can be done on queries within the stored
> procedure or queries that use the stored procedure (say as the column in a
> view). I hav
Damon Snyder wrote:
> I have heard it said that if a stored procedure is declared as
> VOLATILE, then no good optimizations can be done on queries within
> the stored procedure or queries that use the stored procedure (say
> as the column in a view). I have seen this in practice, recommended
> o
Hello,
I have heard it said that if a stored procedure is declared as VOLATILE,
then no good optimizations can be done on queries within the stored
procedure or queries that use the stored procedure (say as the column in a
view). I have seen this in practice, recommended on the irc channel, and in
n Corp.
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 26, 2006 9:15 AM
> To: Matthew Peters
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Stored procedure slower than sql?
> Importance: High
>
> "Matt
@postgresql.org
Subject: Re: [PERFORM] Stored procedure slower than sql?
Importance: High
"Matthew Peters" <[EMAIL PROTECTED]> writes:
> How can a stored procedure containing a single query not implement the
> same execution plan (assumption based on the dramatic performance
"Matthew Peters" <[EMAIL PROTECTED]> writes:
> How can a stored procedure containing a single query not implement the
> same execution plan (assumption based on the dramatic performance
> difference) that an identical ad-hoc query generates?
Parameterized vs non parameterized query?
(Repost - did not appear to make it to the list the first time)
I have written a stored procedure for 8.1 that wraps a single (albeit complex)
query, and uses 2 IN parameters (BIGINT, INTEGER) in the FROM JOIN and WHERE
clauses. The procedure is written in SQL (as opposed to plpgsql - although
On 4/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Merlin Moncure wrote:
> > pl/pgsql procedures are a very thin layer over the query engine.
> > Generally, they run about the same speed as SQL but you are not making
> > apples to apples comparison. One of the few but annoying limitations
> >
Merlin Moncure wrote:
> On 4/11/06, Simon Dale <[EMAIL PROTECTED]> wrote:
> > I'm trying to evaluate PostgreSQL as a database that will have to store a
> > high volume of data and access that data frequently. One of the features on
> > our wish list is to be able to use stored procedures to access
commit work (!)
Regards
Henk Sanders
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Merlin Moncure
> Verzonden: dinsdag 11 april 2006 15:50
> Aan: Simon Dale
> CC: pgsql-performance@postgresql.org
> Onderwerp: Re: [PERF
[EMAIL PROTECTED] ("Simon Dale") wrote:
> Event with the planning removed, the function still
> performs
> significantly slower than the raw SQL. Is that normal or am I doing something
> wrong
> with the creation or calling of the
> function?
I'd expect this, yes.
You're doing something via "st
On 4/11/06, Simon Dale <[EMAIL PROTECTED]> wrote:
> I'm trying to evaluate PostgreSQL as a database that will have to store a
> high volume of data and access that data frequently. One of the features on
> our wish list is to be able to use stored procedures to access the data and
> I was wondering
Rajesh Kumar Mallah wrote:
On 4/11/06, Simon Dale <[EMAIL PROTECTED]> wrote:
I'm trying to evaluate PostgreSQL as a database that will have to store a
high volume of data and access that data frequently. One of the features on
our wish list is to be able to use stored procedures to access the d
On 4/11/06, Simon Dale <[EMAIL PROTECTED]> wrote:
>
>
>
> Hi,
>
>
>
> I'm trying to evaluate PostgreSQL as a database that will have to store a
> high volume of data and access that data frequently. One of the features on
> our wish list is to be able to use stored procedures to access the data and
On 4/11/06, Simon Dale <[EMAIL PROTECTED]> wrote:
I'm trying to evaluate PostgreSQL as a database that
will have to store a high volume of data and access that data frequently. One
of the features on our wish list is to be able to use stored procedures to
access the data and I was wond
Hi,
I’m trying to evaluate PostgreSQL as a database that
will have to store a high volume of data and access that data frequently. One
of the features on our wish list is to be able to use stored procedures to
access the data and I was wondering if it is usual for stored procedures to
p
On Tue, Nov 22, 2005 at 11:20:09PM +0100, Yves Vindevogel wrote:
> 8.1, hmm, that's brand new.
Yes, but give it a try, at least in a test environment. The more
people use it, the more we'll find out if it has any problems.
> But, still, it's quite some coding for a complete recordset, not ?
On Tue, Nov 22, 2005 at 11:17:41PM +0100, Yves Vindevogel wrote:
> But this does not work without the second line, right ?
What second line? Instead of returning a specific composite type
a function can return RECORD or SETOF RECORD; in these cases the
query must provide a column definition lis
8.1, hmm, that's brand new.
But, still, it's quite some coding for a complete recordset, not ?
On 22 Nov 2005, at 19:59, Michael Fuhr wrote:
On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote:
Is there another way in PG to return a recordset from a function than
to declare a type
tof RECORD ...
then to call it you would do
select * from abc() as (a text,b int,...);
-- Original Message ---
From: Yves Vindevogel <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sent: Tue, 22 Nov 2005 19:29:37 +0100
Subject: [PERFORM] Stored Procedure
Is there a
On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote:
> Is there another way in PG to return a recordset from a function than
> to declare a type first ?
In 8.1 some languages support OUT and INOUT parameters.
CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$
BE
ERFORM] Stored Procedure
> Is there another way in PG to return a recordset from a function than
> to declare a type first ?
>
> create function fnTest () returns setof
> myDefinedTypeIDontWantToDefineFirst ...
>
> Met vriendelijke groeten,
> Bien à vous,
> K
Is there another way in PG to return a recordset from a function than to declare a type first ?
create function fnTest () returns setof myDefinedTypeIDontWantToDefineFirst ...
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: [EMAIL PROTECTED] - Mobile:
42 matches
Mail list logo
