On Thu, Jun 16, 2016 at 8:40 PM, Erdmann, Markus @ Bellevue <
markus.erdm...@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.
>
> 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 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)
>
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.
