Re: Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

Hello,

We’re trying to debug a performance issue affecting our staging database, and we’ve narrowed it down to a difference in the query optimizer in 9.5.2. Upgrading to 9.5 is important for us because we need the ability to import foreign schemas.

This is the query we’re running:

CREATE TEMP TABLE tmp_joined_transactions_9gkgptn5xcp9 ( transaction_id integer PRIMARY KEY );

INSERT INTO tmp_joined_transactions_9gkgptn5xcp9 (transaction_id)

SELECT DISTINCT ON ("transactions_transaction"."id") "transactions_transaction"."id"

FROM "transactions_transaction" , "tmp_joined_transactions_75chlsokrsev"

WHERE

(NOT ("transactions_transaction"."id"

IN (SELECT U0."id" AS Col1

FROM "transactions_transaction" U0

LEFT OUTER JOIN "transactions_commission" U1

ON ( U0."id" = U1."transaction_id" )

WHERE U1."id" IS NULL)) 

AND "transactions_transaction"."date_created" >= '2010-01-01'::date

AND "transactions_transaction"."date_created" <= '2015-12-31'::date

AND "transactions_transaction"."deal_status" IN (1)

AND (transactions_transaction.id = tmp_joined_transactions_75chlsokrsev.transaction_id))

ORDER BY "transactions_transaction"."id" ASC;