Upgrading from 9.2 to 9.3 causes performance degradation

On Monday, March 24, 2014, Álvaro Nunes Lemos Melo
<al_nunes(at)atua(dot)com(dot)br<javascript:_e(%7B%7D,'cvml','al_nunes(at)atua(dot)com(dot)br');>>
wrote:

> Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change
> was notices, the explain is availiable in
>
> 9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW
> 9.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