From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jan Nielsen <jan.sture.nielsen@gmail.com>
To: Jim Finnerty <jfinnert@amazon.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT performance drop
Date: Wed, 23 Jan 2019 23:41:03 +0100
Message-ID: <a42bbd33ff0c4fe6e8d6c805363f1e720330f941.camel@cybertec.at> (raw)
In-Reply-To: <CANxH4hGOXu+tumYTnstPD=unuBKHj5BXVvUydsc5dL1gLgPwKQ@mail.gmail.com>
References: <CANxH4hECtKo1+O38ZZ6cxVX3kua+n1rQz2FjSw-=f9U9g1c+Ow@mail.gmail.com>
<CANxH4hF6HWrJTE41X77MeZ9U0wQd+Cc7JZwbrhbw=wSskggEAQ@mail.gmail.com>
<1548251452315-0.post@n3.nabble.com>
<CANxH4hGOXu+tumYTnstPD=unuBKHj5BXVvUydsc5dL1gLgPwKQ@mail.gmail.com>
On Wed, 2019-01-23 at 10:28 -0700, Jan Nielsen wrote:
> select
> order0_.id as id1_7_,
> order0_.created_by as created_2_7_,
> order0_.created_date as created_3_7_,
> order0_.last_modified_by as last_mod4_7_,
> order0_.last_modified_date as last_mod5_7_,
> order0_.consumer as consumer6_7_,
> order0_.market_id as market_14_7_,
> order0_.original as original7_7_,
> order0_.owner_id as owner_i15_7_,
> order0_.owner_target as owner_ta8_7_,
> order0_.price as price9_7_,
> order0_.session_id as session16_7_,
> order0_.side as side10_7_,
> order0_.supplier as supplie11_7_,
> order0_.type as type12_7_,
> order0_.units as units13_7_
> from
> fm_order order0_ cross
> join
> fm_session session1_
> where
> order0_.session_id=session1_.id
> and order0_.type='LIMIT'
> and session1_.original=7569
> and (
> order0_.consumer is null
> or order0_.consumer>0
> and (
> exists (
> select
> 1
> from
> fm_order order2_ cross
> join
> fm_session session3_
> where
> order2_.session_id=session3_.id
> and order2_.id=order0_.consumer
> and session3_.original=7569
> and order2_.type='LIMIT'
> and order2_.owner_id<>order0_.owner_id
> )
> )
> )
> order by
> order0_.last_modified_date DESC;
It might be more efficient to rewrite that along these lines:
SELECT DISTINCT order0_.*
FROM fm_order order0_
JOIN fm_session session1_ ON order0_.session_id = session1_.id
LEFT JOIN fm_order order2_ ON order2_.id = order0_.consumer
LEFT JOIN fm_session session3_ ON order2_.session_id = session3_.id
WHERE coalesce(order2_.id, 1) > 0
AND /* all the other conditions */;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, jan.sture.nielsen@gmail.com, jfinnert@amazon.com
Subject: Re: SELECT performance drop
In-Reply-To: <a42bbd33ff0c4fe6e8d6c805363f1e720330f941.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox