Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change was notices, the explain is availiable in9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW9.3 - Original - http://explain.depesz.com/s/Vwt
OK, thanks. Your plans (and query) are very unwieldy and hard to think about. If you took just one of the selects that participate in the UNION ALL chain, can you reproduce the problem there?
Also, what is this supposed to do:
AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
FROM pessoa p_
WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
You are selecting for p, not p_, so I don't understand what role p_ even plays in that. I wonder of the work done on LATERAL in 9.3 did something here.
Adrian: I'll try to send the query attached. My doubts are: why 9.3 is slower then 9.2 with the same database/configuration/harware/query? Even worst, why 9.3 got slower results on a better HW, where 9.2 improved on the same scenario.
Any changed to complicated systems will have some unintended and unfortunate consequences. Few people spend time investigating all of the queries that now run faster in 9.3!
How big is your database? Any chance you can upload the relevant tables someplace public, or write a generator that creates fake data which still displays the problem?
Or, can you compile your own PostgreSQL from git checkouts? If so, running git bisect to see just where the problem started in the development process might be the faster than other investigation methods.
Cheers,
Jeff
