On Thu, Jun 16, 2016 at 8:40 PM, Erdmann, Markus @ Bellevue <Markus.Erdmann@cbre.com> wrote:
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.
INSERT INTO tmp_joined_transactions_9gkgptn5xcp9 (transaction_id)
This...
SELECT DISTINCT ON ("transactions_transaction"."id") "transactions_transaction"."id"
DISTINCT is a code smell. DISTINCT ON less so - it helps to avoid self-joins - but your inclusion of ON here is pointless since the only output column is "id".
As written there should be no way to get duplicate "id"s into the output result. Or, if the tmp_joined_transactions relationship is 1-to-many you should instead use a semi-join instead of an inner join.
FROM "transactions_transaction" , "tmp_joined_transactions_75chlsokrsev"
WHERE
Here...
(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))
Making this an anti-join (NOT EXISTS instead of NOT IN) stands out as an easy improvement to try:
It also makes the logic clearer since you seem to have a double-negative here which means you really want a semi-join (which I wrote below)
WHERE EXISTS (SELECT 1 FROM transactions_commission WHERE transactions_transaction.id = transactions_commission.transaction_id)
I won't promise this gives the same answer...I don't have enough spare brain power or the ability to test it...but its seems correct.
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)