Hello everyone, I am having a strange performance issue concerning the creation of a materialized view in Postgres 9.6.
I have a somewhat complex query, that takes about two minutes to fully run and that I want to run often, therefore I want to create a materialized view of this query to speed things up. But when I try to create the materialized I get hours of processing just to eventually crash at a not enough memory for temporary files error. At first I thought my indexes were fucked up, but I as said early the selection query itself don't take more than two minutes and the indexes are working fine. I also tried to change the temporary files directory to a bigger (slower) hard disk. After four hours of processing the temporary files were summing 600 gb of memory (about twenty times the size of my whole database) and I had to send a stop sign. Today I just tried to create a normal table and everything fine under 3 minutes of processing time. The selection query goes as: select c.ano, c.mes, a.carreira_id as id, avg(r.rem_bruta) as salmed, median(r.rem_bruta) as selmediana, stddev_pop(r.rem_bruta) as salsd, avg(r.indenizacao_total) as indmed, median(r.indenizacao_total) as indmediana, stddev_pop(r.indenizacao_total) as indsd from servidores.cad c left join servidores.cargo a on c.cargo_id = a.id join servidores.rem r on c.ano = r.ano and c.mes = r.mes and c.rem_id = r.id group by c.ano, c.mes, a.carreira_id);
