Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

- http://wiki.postgresql.org/wiki/Performance_Optimization - run it on the most powerful machine you can find - get some more memory - get a big (512-1TB) SSD drive - avoid recalculating the same things over and over. if your views have many similar elements, then calculate those first into a par

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Excellent. Maybe the last sub-question : Those 3600 mat views do have *indexes*. I guess I will get better performances in *dropping indexes* first, then refresh, then *re-creating indexes*. Are there other way to improve performances (like mat views storage parameters

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Hi again Nick. Glad it helped. Generally, I would expect that doing all the A's first, then all the B's, and so on, would be fastest since you can re-use the data from cache. Concurrency when reading isn't generally a problem. Lots of things can read at the same time and it will be nice and

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Hello, Thanks for this clear explanation ! Then I have a sub-question : Supposed I have 3600 materialised views say 600 mat views from 6 main table. (A,B,C,D,E,F are repetead 600 times with some differences) Is it faster to : 1) parallel refresh 600 time A, then 600 time B etc, OR 2) parallel ref

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 04 Apr 2014, at 18:29, Nicolas Paris wrote: > Hello, > > My question is about multiprocess and materialized View. > http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html > I (will) have something like 3600 materialised views, and I would like to > know the way to refresh

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Right, not refering triggers, seems to be kind of mix C/sql compiled (= external). To conclude : - pl/proxy, it appears difficult, and not designed to. - pgAgent (supposed to apply jobs in a multithreaded way) - bash (xargs does the job) - external scripts (R, python, perl...) So I will test pgAg

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 4 April 2014 21:26, PARIS Nicolas wrote: > this postgres documentation : > http://www.postgresql.org/docs/9.3/static/ecpg-connect.html > says it is actually possible to manage connection in C stored procedure. > > I may be wrong... That page doesn't refer to triggers at all, so I'm still not s

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On Fri, Apr 04, 2014 at 10:26:22PM +0200, PARIS Nicolas wrote: > this postgres documentation : > http://www.postgresql.org/docs/9.3/static/ecpg-connect.html > says it is actually possible to manage connection in C stored procedure. > > I may be wrong... > > > Le 04/04/2014 22:14, Thom Brown a éc

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

this postgres documentation : http://www.postgresql.org/docs/9.3/static/ecpg-connect.html says it is actually possible to manage connection in C stored procedure. I may be wrong... Le 04/04/2014 22:14, Thom Brown a écrit : > lear on how triggers come into this. You can't have triggers > on mate

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Ok thanks, And what about triggers. 8 triggers based on the same event won't be multithreaded ? Le 04/04/2014 21:57, Thom Brown a écrit : > On 4 April 2014 20:49, PARIS Nicolas wrote: >> Thanks, >> >> "The only thing that immediately comes to mind would be running a >> rather hacky DO functi

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 4 April 2014 21:07, PARIS Nicolas wrote: > Ok thanks, > > And what about triggers. 8 triggers based on the same event won't be > multithreaded ? I'm not clear on how triggers come into this. You can't have triggers on materialized views, and they don't fire triggers on tables or views that th

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 4 April 2014 20:49, PARIS Nicolas wrote: > Thanks, > > "The only thing that immediately comes to mind would be running a > rather hacky DO function in 4 separate sessions:" > You mean 8 sessions I guess. Yes, typo. > 8 separate sessions ? > Have you any idea how to manage sessions ? Is it po

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Thanks, "The only thing that immediately comes to mind would be running a rather hacky DO function in 4 separate sessions:" You mean 8 sessions I guess. 8 separate sessions ? Have you any idea how to manage sessions ? Is it possible to create separate session internaly ? Do I have to make 8 exte

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 4 April 2014 17:29, Nicolas Paris wrote: > Hello, > > My question is about multiprocess and materialized View. > http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html > I (will) have something like 3600 materialised views, and I would like to > know the way to refresh them i

[PERFORM] PGSQL 9.3 - Materialized View - multithreading

Hello, My question is about multiprocess and materialized View. http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html I (will) have something like 3600 materialised views, and I would like to know the way to refresh them in a multithread way (anderstand 8 cpu cores -> 8 refresh