Re: [PERFORM] blocking index creation

[Scott Marlowe]

Try the queries here to check locks:

https://wiki.postgresql.org/wiki/Lock_Monitoring

On Wed, Oct 11, 2017 at 7:35 PM, Neto pr  wrote:
> Dear,
> With alternative, I tested the creation using concurrency
> (CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);
>
> from what I saw the index already appeared in the query result, because
> before this, the index did not even appear in the result, only the Lineitem
> table:
>
> SELECT
>   L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
> virtualtransaction
> FROM pg_locks l, pg_class c
> where c.oid = l.relation
>
> screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg
>
> Now, I'm waiting to finish creating the index.
>
> 2017-10-11 19:54 GMT-03:00 Neto pr :
>>
>> Hello all,
>> I ran the query on PG_STAT_ACTIVITY table (Select * From
>> pg_stat_activity),  see the complete result in this worksheet of the link
>> below.
>>
>>
>> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls
>>
>> The CREATE INDEX command line is identified with the orange background.
>> At this point 18 hours have passed and the creation of a single index has
>> not yet been completed.
>> I have verified that the command is Active status, but I do not know if
>> it's waiting for anything, can you help me analyze the attached output.
>>
>> Regards
>> Neto
>>
>> 2017-10-11 18:08 GMT-03:00 Tomas Vondra :
>>>
>>>
>>>
>>> On 10/11/2017 04:11 PM, Neto pr wrote:
>>> >
>>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe >> > >:
>>> >
>>> > Neto pr wrote:
>>> > > When creating index on table of approximately 10GB of data, the
>>> > DBMS hangs (I think),
>>> > > because even after waiting 10 hours there was no return of the
>>> > command.
>>> > > It happened by creating Hash indexes and B + tree indexes.
>>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>>> > L_PARTKEY).
>>> >
>>> > > If someone has a hint how to speed up index creation so that it
>>> > completes successfully.
>>> >
>>> > Look if CREATE INDEX is running or waiting for a lock (check the
>>> > "pg_locks" table, see if the backend consumes CPU time).
>>> >
>>> >
>>> > In this moment now, there is an index being created in the Lineitem
>>> > table (+ - 10 Gb), and apparently it is locked, since it started 7
>>> > hours
>>> > ago.
>>> > I've looked at the pg_locks table and look at the result, it's with
>>> > "ShareLock" lock mode.
>>> > Is this blocking correct? or should it be another type?
>>> >
>>>
>>> Yes, CREATE INDEX acquire SHARE lock, see
>>>
>>>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>>
>>> > Before creating the index, should I set the type of transaction lock?
>>> > What?
>>>
>>> Eeee? Not sure I understand. The command acquires all necessary locks
>>> automatically.
>>>
>>> >
>>> > ---
>>> > SELECT
>>> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
>>> > virtualtransaction
>>> > FROM   pg_locks l, pg_class   c
>>> > where  c.oid = l.relation
>>> >
>>> > -- RESULT
>>> > --
>>> > AccessShareLock   pg_class_tblspc_relfilenode_index   relation
>>> > TRUE
>>> > (null)3/71
>>> > AccessShareLock   pg_class_relname_nsp_index  relation
>>> > TRUE(null)  3/71
>>> > AccessShareLock   pg_class_oid_index  relationTRUE
>>> > (null)  3/71
>>> > AccessShareLock   pg_classrelationTRUE(null)
>>> > 3/71
>>> > AccessShareLock   pg_locksrelationTRUE(null)
>>> > 3/71
>>> > ShareLock lineitemrelationTRUE(null)  21/3769
>>> >
>>> >
>>>
>>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>>> but we don't really know what the session is doing.
>>>
>>> There's a PID in the pg_locks table, you can use it to lookup the
>>> session in pg_stat_activity which includes the query (and also "state"
>>> column that will tell you if it's active or waiting for a lock.
>>>
>>> regards
>>>
>>> --
>>> Tomas Vondra  http://www.2ndQuadrant.com
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>>
>



-- 
To understand recursion, one must first understand recursion.


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

Re: [PERFORM] blocking index creation

[Neto pr]

Dear,
With alternative, I tested the creation using concurrency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);

from what I saw the index already appeared in the query result, because
before this, the index did not even appear in the result, only the Lineitem
table:

SELECT
  L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation

screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating the index.

2017-10-11 19:54 GMT-03:00 Neto pr :

> Hello all,
> I ran the query on PG_STAT_ACTIVITY table (Select * From
> pg_stat_activity),  see the complete result in this worksheet of the link
> below.
>
> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_
> activity-create_index.xls
>
> The CREATE INDEX command line is identified with the orange background.
> At this point 18 hours have passed and the creation of a single index has
> not yet been completed.
> I have verified that the command is Active status, but I do not know if
> it's waiting for anything, can you help me analyze the attached output.
>
> Regards
> Neto
>
> 2017-10-11 18:08 GMT-03:00 Tomas Vondra :
>
>>
>>
>> On 10/11/2017 04:11 PM, Neto pr wrote:
>> >
>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe > > >:
>> >
>> > Neto pr wrote:
>> > > When creating index on table of approximately 10GB of data, the
>> DBMS hangs (I think),
>> > > because even after waiting 10 hours there was no return of the
>> command.
>> > > It happened by creating Hash indexes and B + tree indexes.
>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>> L_PARTKEY).
>> >
>> > > If someone has a hint how to speed up index creation so that it
>> completes successfully.
>> >
>> > Look if CREATE INDEX is running or waiting for a lock (check the
>> > "pg_locks" table, see if the backend consumes CPU time).
>> >
>> >
>> > In this moment now, there is an index being created in the Lineitem
>> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
>> > ago.
>> > I've looked at the pg_locks table and look at the result, it's with
>> > "ShareLock" lock mode.
>> > Is this blocking correct? or should it be another type?
>> >
>>
>> Yes, CREATE INDEX acquire SHARE lock, see
>>
>>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>
>> > Before creating the index, should I set the type of transaction lock?
>> What?
>>
>> Eeee? Not sure I understand. The command acquires all necessary locks
>> automatically.
>>
>> > 
>> ---
>> > SELECT
>> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
>> > virtualtransaction
>> > FROM   pg_locks l, pg_class   c
>> > where  c.oid = l.relation
>> >
>> > -- RESULT
>> > --
>> > AccessShareLock   pg_class_tblspc_relfilenode_index
>>  relationTRUE
>> > (null)3/71
>> > AccessShareLock   pg_class_relname_nsp_index  relation
>> TRUE(null)  3/71
>> > AccessShareLock   pg_class_oid_index  relationTRUE
>> (null)  3/71
>> > AccessShareLock   pg_classrelationTRUE(null)
>> 3/71
>> > AccessShareLock   pg_locksrelationTRUE(null)
>> 3/71
>> > ShareLock lineitemrelationTRUE(null)  21/3769
>> >
>> >
>>
>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>> but we don't really know what the session is doing.
>>
>> There's a PID in the pg_locks table, you can use it to lookup the
>> session in pg_stat_activity which includes the query (and also "state"
>> column that will tell you if it's active or waiting for a lock.
>>
>> regards
>>
>> --
>> Tomas Vondra  http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

Hello all,
I ran the query on PG_STAT_ACTIVITY table (Select * From
pg_stat_activity),  see the complete result in this worksheet of the link
below.

https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls

The CREATE INDEX command line is identified with the orange background.
At this point 18 hours have passed and the creation of a single index has
not yet been completed.
I have verified that the command is Active status, but I do not know if
it's waiting for anything, can you help me analyze the attached output.

Regards
Neto

2017-10-11 18:08 GMT-03:00 Tomas Vondra :

>
>
> On 10/11/2017 04:11 PM, Neto pr wrote:
> >
> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe  > >:
> >
> > Neto pr wrote:
> > > When creating index on table of approximately 10GB of data, the
> DBMS hangs (I think),
> > > because even after waiting 10 hours there was no return of the
> command.
> > > It happened by creating Hash indexes and B + tree indexes.
> > > However, for some columns, it was successfully (L_RETURNFLAG,
> L_PARTKEY).
> >
> > > If someone has a hint how to speed up index creation so that it
> completes successfully.
> >
> > Look if CREATE INDEX is running or waiting for a lock (check the
> > "pg_locks" table, see if the backend consumes CPU time).
> >
> >
> > In this moment now, there is an index being created in the Lineitem
> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> > ago.
> > I've looked at the pg_locks table and look at the result, it's with
> > "ShareLock" lock mode.
> > Is this blocking correct? or should it be another type?
> >
>
> Yes, CREATE INDEX acquire SHARE lock, see
>
>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>
> > Before creating the index, should I set the type of transaction lock?
> What?
>
> Eeee? Not sure I understand. The command acquires all necessary locks
> automatically.
>
> > 
> ---
> > SELECT
> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
> > virtualtransaction
> > FROM   pg_locks l, pg_class   c
> > where  c.oid = l.relation
> >
> > -- RESULT
> > --
> > AccessShareLock   pg_class_tblspc_relfilenode_index   relation
>   TRUE
> > (null)3/71
> > AccessShareLock   pg_class_relname_nsp_index  relation
> TRUE(null)  3/71
> > AccessShareLock   pg_class_oid_index  relationTRUE
> (null)  3/71
> > AccessShareLock   pg_classrelationTRUE(null)
> 3/71
> > AccessShareLock   pg_locksrelationTRUE(null)
> 3/71
> > ShareLock lineitemrelationTRUE(null)  21/3769
> >
> >
>
> Well, we see something is holding a SHARE lock on the "lineitem" table,
> but we don't really know what the session is doing.
>
> There's a PID in the pg_locks table, you can use it to lookup the
> session in pg_stat_activity which includes the query (and also "state"
> column that will tell you if it's active or waiting for a lock.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [PERFORM] blocking index creation

[Tomas Vondra]

On 10/11/2017 04:11 PM, Neto pr wrote:
> 
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe  >:
> 
> Neto pr wrote:
> > When creating index on table of approximately 10GB of data, the DBMS 
> hangs (I think),
> > because even after waiting 10 hours there was no return of the command.
> > It happened by creating Hash indexes and B + tree indexes.
> > However, for some columns, it was successfully (L_RETURNFLAG, 
> L_PARTKEY).
> 
> > If someone has a hint how to speed up index creation so that it 
> completes successfully.
> 
> Look if CREATE INDEX is running or waiting for a lock (check the
> "pg_locks" table, see if the backend consumes CPU time).
> 
> 
> In this moment now, there is an index being created in the Lineitem
> table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> ago.
> I've looked at the pg_locks table and look at the result, it's with
> "ShareLock" lock mode.
> Is this blocking correct? or should it be another type?
> 

Yes, CREATE INDEX acquire SHARE lock, see

   https://www.postgresql.org/docs/9.1/static/explicit-locking.html

> Before creating the index, should I set the type of transaction lock? What?

Eeee? Not sure I understand. The command acquires all necessary locks
automatically.

> ---
> SELECT
>   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
> virtualtransaction
> FROM   pg_locks l, pg_class   c
> where  c.oid = l.relation
> 
> -- RESULT
> --
> AccessShareLock   pg_class_tblspc_relfilenode_index   relation
> TRUE
> (null)3/71
> AccessShareLock   pg_class_relname_nsp_index  relationTRUE
> (null)  3/71
> AccessShareLock   pg_class_oid_index  relationTRUE(null)  
> 3/71
> AccessShareLock   pg_classrelationTRUE(null)  3/71
> AccessShareLock   pg_locksrelationTRUE(null)  3/71
> ShareLock lineitemrelationTRUE(null)  21/3769
> 
>  

Well, we see something is holding a SHARE lock on the "lineitem" table,
but we don't really know what the session is doing.

There's a PID in the pg_locks table, you can use it to lookup the
session in pg_stat_activity which includes the query (and also "state"
column that will tell you if it's active or waiting for a lock.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [PERFORM] Stored Procedure Performance

[Pavel Stehule]

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

If it is possible, don't do it in cycle, or use exception handling only
when it is necessary, not from pleasure.

Regards

Pavel


> Thanks
>
> On 11 October 2017 at 22:07, Adam Brusselback 
> wrote:
>
>> Is there any error handling in there?  I remember seeing performance
>> issues if you put in any code to catch exceptions.
>>
>
>

Re: [PERFORM] Stored Procedure Performance

[Adam Brusselback]

> 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 the INSERT.


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

Re: [PERFORM] Stored Procedure Performance

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

Thanks

On 11 October 2017 at 22:07, Adam Brusselback 
wrote:

> Is there any error handling in there?  I remember seeing performance
> issues if you put in any code to catch exceptions.
>

Re: [PERFORM] Stored Procedure Performance

[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

Re: [PERFORM] Stored Procedure Performance

[Purav Chovatia]

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 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?
>>
>
> PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
> dependency on environment that executed it.
>
> This sentence mean, so PLpgSQL is not designed for intensive mathematics
> calculation.  PL/SQL is self govering environment ... it has own data
> types, it has own implementation of logical and mathematics operators.
> PLpgSQL is layer over SQL engine - and has not own types, has not own
> operators. Any expression is translated to SQL and then is interpreted by
> SQL expression interpret. Maybe in next few years there will be a JIT
> compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
> PL code is glue for SQL queries (implementation of some business
> processes), then PLpgSQL is fast enough. If you try to calculate numeric
> integration or derivation of some functions, then PLpgSQL is slow. It is
> not too slow - the speed is comparable with PHP, but it is significantly
> slower than C language.
>
> PostgreSQL has perfect C API - so intensive numeric calculations are
> usually implemented as C extension.
>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> On 3 October 2017 at 20:24, Laurenz Albe 
>> wrote:
>>
>>> 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 truncated
>>> before every performance test.
>>> >
>>> > We are doing about 100 executions of both of these stored proc per
>>> second.
>>> >
>>> > In Oracle each exec takes about 1millisec whereas in postgres its
>>> taking 10millisec and that eventually leads to a queue build up in our
>>> application.
>>> >
>>> > All indices are in place. The select, insert & update are all single
>>> row operations and use the PK.
>>> >
>>> > It does not look like any query taking longer but something else. How
>>> can I check where is the time being spent? There are no IO waits, so its
>>> all on the CPU.
>>>
>>> You could profile the PostgreSQL server while it is executing the
>>> workload,
>>> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>>>
>>> That way you could see where the time is spent.
>>>
>>> PL/pgSQL is not optimized for performance like PL/SQL.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>
>>
>

Re: [PERFORM] Stored Procedure Performance

[Pavel Stehule]

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 option as compared to
> putting those in Stored Procs?
>

PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
dependency on environment that executed it.

This sentence mean, so PLpgSQL is not designed for intensive mathematics
calculation.  PL/SQL is self govering environment ... it has own data
types, it has own implementation of logical and mathematics operators.
PLpgSQL is layer over SQL engine - and has not own types, has not own
operators. Any expression is translated to SQL and then is interpreted by
SQL expression interpret. Maybe in next few years there will be a JIT
compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
PL code is glue for SQL queries (implementation of some business
processes), then PLpgSQL is fast enough. If you try to calculate numeric
integration or derivation of some functions, then PLpgSQL is slow. It is
not too slow - the speed is comparable with PHP, but it is significantly
slower than C language.

PostgreSQL has perfect C API - so intensive numeric calculations are
usually implemented as C extension.

Regards

Pavel


>
> Regards
>
> On 3 October 2017 at 20:24, Laurenz Albe  wrote:
>
>> 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 truncated
>> before every performance test.
>> >
>> > We are doing about 100 executions of both of these stored proc per
>> second.
>> >
>> > In Oracle each exec takes about 1millisec whereas in postgres its
>> taking 10millisec and that eventually leads to a queue build up in our
>> application.
>> >
>> > All indices are in place. The select, insert & update are all single
>> row operations and use the PK.
>> >
>> > It does not look like any query taking longer but something else. How
>> can I check where is the time being spent? There are no IO waits, so its
>> all on the CPU.
>>
>> You could profile the PostgreSQL server while it is executing the
>> workload,
>> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>>
>> That way you could see where the time is spent.
>>
>> PL/pgSQL is not optimized for performance like PL/SQL.
>>
>> Yours,
>> Laurenz Albe
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

2017-10-11 10:46 GMT-03:00 Laurenz Albe :

> Neto pr wrote:
> > When creating index on table of approximately 10GB of data, the DBMS
> hangs (I think),
> > because even after waiting 10 hours there was no return of the command.
> > It happened by creating Hash indexes and B + tree indexes.
> > However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
>
> > If someone has a hint how to speed up index creation so that it
> completes successfully.
>
> Look if CREATE INDEX is running or waiting for a lock (check the
> "pg_locks" table, see if the backend consumes CPU time).
>
>
In this moment now, there is an index being created in the Lineitem table
(+ - 10 Gb), and apparently it is locked, since it started 7 hours ago.
I've looked at the pg_locks table and look at the result, it's with
"ShareLock" lock mode.
Is this blocking correct? or should it be another type?

Before creating the index, should I set the type of transaction lock? What?
---
SELECT
  L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

-- RESULT
--
AccessShareLock pg_class_tblspc_relfilenode_index relation TRUE (null) 3/71
AccessShareLock pg_class_relname_nsp_index relation TRUE (null) 3/71
AccessShareLock pg_class_oid_index relation TRUE (null) 3/71
AccessShareLock pg_class relation TRUE (null) 3/71
AccessShareLock pg_locks relation TRUE (null) 3/71
ShareLock lineitem relation TRUE (null) 21/3769

> Maybe there is a long-running transaction that blocks the
> ACCESS EXCLUSIVE lock required.  It could also be a prepared
> transaction.
>
> Yours,
> Laurenz Albe
>

Best Regards
Neto

Re: [PERFORM] Stored Procedure Performance

[Purav Chovatia]

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 such execution stats, and hence we are
planning to add this extension as a default to all our production
deployments.

Its a single row select using PK, single row update using PK and a single
row insert, so I dont see anything wrong with the code. So auto_explain
would not add any value, I believe.

Basically, on an Oracle server, I would minimally look at statspack/awr
report & OS stats (like cpu, iostat & memory) to start with. What should I
look for in case of a Postgres server.

Thanks & Regards

On 3 October 2017 at 20:58, Pavel Stehule  wrote:

>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback :
>
>> There is also the option of pg_stat_statements: https://ww
>> w.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 you should be seeing a 10x slowdown between
>> Postgres and Oracle, so you'll likely have to just profile it to find out.
>>
>
> depends what is inside.
>
> The max 10x slow down is possible if you are hit some unoptimized cases.
> The times about 1ms - 10ms shows so procedure (code) can be very sensitive
> to some impacts.
>
> Regards
>
> Pavel
>
>

Re: [PERFORM] Stored Procedure Performance

[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 option as compared to
putting those in Stored Procs?

Regards

On 3 October 2017 at 20:24, Laurenz Albe  wrote:

> 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 truncated
> before every performance test.
> >
> > We are doing about 100 executions of both of these stored proc per
> second.
> >
> > In Oracle each exec takes about 1millisec whereas in postgres its taking
> 10millisec and that eventually leads to a queue build up in our application.
> >
> > All indices are in place. The select, insert & update are all single row
> operations and use the PK.
> >
> > It does not look like any query taking longer but something else. How
> can I check where is the time being spent? There are no IO waits, so its
> all on the CPU.
>
> You could profile the PostgreSQL server while it is executing the
> workload,
> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>
> That way you could see where the time is spent.
>
> PL/pgSQL is not optimized for performance like PL/SQL.
>
> Yours,
> Laurenz Albe
>

Re: [PERFORM] blocking index creation

[Laurenz Albe]

Neto pr wrote:
> When creating index on table of approximately 10GB of data, the DBMS hangs (I 
> think),
> because even after waiting 10 hours there was no return of the command.
> It happened by creating Hash indexes and B + tree indexes.
> However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).

> If someone has a hint how to speed up index creation so that it completes 
> successfully.

Look if CREATE INDEX is running or waiting for a lock (check the
"pg_locks" table, see if the backend consumes CPU time).

Maybe there is a long-running transaction that blocks the
ACCESS EXCLUSIVE lock required.  It could also be a prepared
transaction.

Yours,
Laurenz Albe


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

[PERFORM] blocking index creation

[Neto pr]

Hello all,

My scenario is: postgresql 10, Processor Xeon 2.8GHz / 4-core- 8gb Ram, OS
Debian 8.

When creating index on table of approximately 10GB of data, the DBMS hangs
(I think), because even after waiting 10 hours there was no return of the
command. It happened by creating Hash indexes and B + tree indexes.
However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
The data environment is the LINEITEM table (TPC-H benchmark) of link 1
below. The
columns/indexes that caught the creation were: * Hash Index in column:
L_TAX * Btree Index in column: L_RECEIPTDATE.

If someone has a hint how to speed up index creation so that it completes
successfully. I know that PostgreSQL 10 has some parallelism features and
since my server is dedicated only to the DBMS, do I change the parameters:
force_parallel_mode, max_parallel_workers_per_gather could speed up index
creation on large tables? Any tip is welcome.

DDL comand :
L_ORDERKEY BIGINT NOT NULL, - references O_ORDERKEY
L_PARTKEY BIGINT NOT NULL, - references P_PARTKEY (compound fk to PARTSUPP)
L_SUPPKEY BIGINT NOT NULL, - references S_SUPPKEY (compound fk to PARTSUPP)
L_LINENUMBER INTEGER,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT VARCHAR (44),PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)

1- http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png

best Regards

Neto

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

[johannes graën]

On Wed, Oct 11, 2017 at 1:11 PM, Pavel Stehule  wrote:
> have you fresh statistics? After upgrade is necessary to run ANALYZE command

Yes, that was missing indeed. I did ANALYZE but apparently on all
databases but this one. I could have guessed that
1,098,956,679,131,935,754,413,282,631,696,252,928 is not a reasonable
cost value.

Thanks, Pavel.

Best
  Johannes


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

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

[Pavel Stehule]

2017-10-11 13:06 GMT+02:00 johannes graën :

> Hi,
>
> I wrote a query that joins several tables usually returning less than
> 1000 rows, groups them and generates a JSON object of the result. In
> 9.6 is was a question of milliseconds for that query to return the
> requested data. Now, after upgrading to 10, the query never returns -
> at least it hasn't returned in the last hour.
>
> To see what happens, I requested the query plan [1]. It looks complex
> and shows a lot of parallelization. I don't have the query plan from
> 9.6, but I remember it being considerably simpler.
>
> Can anyone have a guess what altered the performance here so
> dramatically? Is there a way to disable new parallelization features
> just for this query to see if it makes any difference?
>
>

have you fresh statistics? After upgrade is necessary to run ANALYZE command

Regards

Pavel


Best
>   Johannes
>
>
> [1] https://explain.depesz.com/s/xsPP
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

[PERFORM] performance drop after upgrade (9.6 > 10)

[johannes graën]

Hi,

I wrote a query that joins several tables usually returning less than
1000 rows, groups them and generates a JSON object of the result. In
9.6 is was a question of milliseconds for that query to return the
requested data. Now, after upgrading to 10, the query never returns -
at least it hasn't returned in the last hour.

To see what happens, I requested the query plan [1]. It looks complex
and shows a lot of parallelization. I don't have the query plan from
9.6, but I remember it being considerably simpler.

Can anyone have a guess what altered the performance here so
dramatically? Is there a way to disable new parallelization features
just for this query to see if it makes any difference?

Best
  Johannes


[1] https://explain.depesz.com/s/xsPP


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

[PERFORM] Rowcount estimation changes based on from clause order

[Ants Aasma]

I stumbled upon a severe row count underestimation that confusingly
went away when two inner joins in the from clause were reordered. I
whittled it down to a reproducible test case.

Schema:

CREATE TABLE small (id serial primary key, ref_id int not null, subset
int not null);
CREATE TABLE big (id serial primary key, small_id int not null);

INSERT INTO small (ref_id, subset) SELECT i/2+1, i/2+1 FROM
generate_series(1,1000) i;
INSERT INTO big (small_id) SELECT (i % 1000) + 1 FROM
generate_series(1,100) i;

CREATE INDEX ON small (ref_id);
CREATE INDEX ON big (small_id);

ANALYZE;

And the queries, differing in only the order of joins:

SELECT * FROM
  small
  INNER JOIN big ON small.id = big.small_id
  INNER JOIN (SELECT 1 UNION ALL SELECT 2) lookup(ref) ON
lookup.ref = small.ref_id
WHERE small.subset = 42;

SELECT * FROM
  small
  INNER JOIN (SELECT 1 UNION ALL SELECT 2) lookup(ref) ON
lookup.ref = small.ref_id
  INNER JOIN big ON small.id = big.small_id
WHERE small.subset = 42;

Resulting plan for the first case:
 Nested Loop  (cost=20.45..2272.13 rows=8 width=24)
   ->  Nested Loop  (cost=0.28..16.69 rows=1 width=16)
 ->  Append  (cost=0.00..0.04 rows=2 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=4)
   ->  Result  (cost=0.00..0.01 rows=1 width=4)
 ->  Index Scan using small_ref_id_idx on small
(cost=0.28..8.32 rows=1 width=12)
   Index Cond: (ref_id = (1))
   Filter: (subset = 42)
   ->  Bitmap Heap Scan on big  (cost=20.18..2245.44 rows=1000 width=8)
 Recheck Cond: (small_id = small.id)
 ->  Bitmap Index Scan on big_small_id_idx  (cost=0.00..19.93
rows=1000 width=0)
   Index Cond: (small_id = small.id)

Second case plan is identical except row count of the topmost nest loop:
 Nested Loop  (cost=20.45..2272.13 rows=1000 width=24)

The union subselect was in reality somewhat more complicated, but for
the row count issue the simplification does not seem to matter. The
behavior is seen both on 9.4 and on master.

Does anybody have any idea what is going on here? In the real world
case this is based on the estimation was 5 rows instead of 200k, which
resulted in quite bad plan choices downstream.

Regards,
Ants Aasma


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