[PERFORM] Rowcount estimation changes based on from clause order

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

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

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

[PERFORM] blocking index creation

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

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

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

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

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

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] Stored Procedure Performance

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

Re: [PERFORM] Stored Procedure Performance

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?

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] Stored Procedure Performance

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

Re: [PERFORM] blocking index creation

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

Re: [PERFORM] Stored Procedure Performance

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

Re: [PERFORM] Stored Procedure Performance

> 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

Re: [PERFORM] Stored Procedure 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

Re: [PERFORM] Stored Procedure Performance

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

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