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