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)
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 Col1FROM "transactions_transaction" U0LEFT OUTER JOIN "transactions_commission" U1ON ( 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'::dateAND "transactions_transaction"."date_created" <= '2015-12-31'::dateAND "transactions_transaction"."deal_status" IN (1)
Also...
AND (transactions_transaction.id = tmp_joined_transactions_75chlsokrsev.transaction_id))
This is style but I'm really a fan of using ANSI JOIN syntax...turning the above into a <JOIN tmp_* ON>
ORDER BY "transactions_transaction"."id" ASC;
The regression itself someone else would need to comment on.
David J.
