Mailing-List: contact dev-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cayenne.apache.org
Received-SPF: pass (athena.apache.org: message received from 54.191.145.13
 which is an MX secondary for dev@cayenne.apache.org)
MIME-Version: 1.0
Sender: oyvindharboe@gmail.com
In-Reply-To: <A448C122-75BF-4B7E-A423-9A35B8338793@objectstyle.org>
References: 
 <CACELUGfKnJ6OES6c1nrg7iFXHWpjvghURauXeThDy_1wPWj7bg@mail.gmail.com>
	<37CE457F-0457-499D-BB3B-9468D19E6A77@objectstyle.org>
	<CACELUGcXz2nUfpsDxgbD_9U0XmUgTOBmrtp665J7WrXOX=LKgA@mail.gmail.com>
	<347BA30A-1236-48AB-B9E7-D51F91D41C32@objectstyle.org>
	<A448C122-75BF-4B7E-A423-9A35B8338793@objectstyle.org>
Date: Tue, 21 Apr 2015 12:18:28 +0200
Message-ID: 
 <CACELUGf=yynLc5t7Ji4k9QGFsoWgYvME43-T7n3TVSO0uHnBKQ@mail.gmail.com>
Subject: Re: Problem with catastrophic performance degradation under
 PostgreSQL
From: =?UTF-8?Q?=C3=98yvind_Harboe?= <oyvind.harboe@zylin.com>
To: dev@cayenne.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Nice.

Thanks!

Our app is stuck on Cayenne 3 until we can pay off some technical debt.

On Tue, Apr 21, 2015 at 11:55 AM, Andrus Adamchik
<andrus@objectstyle.org> wrote:
> Done. The fix is 4.0-only [1] I optimized the ON(..) loop from O(N2) to O=
(N).
>
> Andrus
>
> [1] https://github.com/apache/cayenne/commit/27a99fab4fb06725cba3e71d2a05=
d9fad129b260
>
>
>> On Apr 21, 2015, at 9:16 AM, Andrus Adamchik <andrus@objectstyle.org> wr=
ote:
>>
>> Looks pretty good to me. I'll run a quick test and commit.
>>
>> Thanks,
>> Andrus
>>
>>> On Apr 20, 2015, at 8:39 PM, =C3=98yvind Harboe <oyvind.harboe@zylin.co=
m> wrote:
>>>
>>> Hi Andrus,
>>>
>>> here's my stab at it. I'm sure it's all sorts of wrong :-) but the
>>> pathological performance drop on PostgreSQL is gone.
>>>
>>> I actually tested this on Cayenne 3 and then copied it over to a patch
>>> on origin/master.
>>>
>>>
>>> ---------- Forwarded message ----------
>>> From: Andrus Adamchik <andrus@objectstyle.org>
>>> Date: Mon, Apr 20, 2015 at 6:07 PM
>>> Subject: Re: Problem with catastrophic performance degradation under Po=
stgreSQL
>>> To: dev@cayenne.apache.org
>>>
>>>
>>> Hi,
>>>
>>> So, Cayenne adds DISTINCT whenever a qualifier includes a to-many
>>> relationship (for obvious reasons). Since we are dealing with an
>>> object query, I guess we always know the ID columns and can rewrite
>>> DISTINCT for PostgreSQL as "DISTINCT ON(id1, id2, ...)" to achieve the
>>> desired effect.
>>>
>>> You want to take a shot at providing a fix for that?
>>>
>>> Thanks,
>>> Andrus
>>>
>>>
>>>> On Apr 20, 2015, at 6:09 PM, =C3=98yvind Harboe <oyvind.harboe@zylin.c=
om> wrote:
>>>>
>>>> Q: Would it be a good idea to modify the PostgreSQL Cayenne adapter to=
 use
>>>> the 'DISTINCT ON()' syntax?
>>>>
>>>> I've been testing out PostgreSQL vs. Derby for our application when I =
ran
>>>> into a problem where the performance of PostgreSQL went from great to
>>>> abysmal for no apparent reason.
>>>>
>>>> After a bit of digging, I've found that the problem is with the SQL
>>>> statement that Cayenne generates.
>>>>
>>>> Cayenne generates statements of the following form which yields bad
>>>> performance on PostgreSQL with complicated WHERE statements and numero=
us
>>>> columns:
>>>>
>>>> 1) SELECT DISTINCT a,b,c,d,e,f ... WHERE somecomplicatedstatement
>>>>
>>>> If I rewrite this statement to the form below using the 'DISTINCT ON()=
'
>>>> syntax(which is PostgreSQL specific dialect), then I get great perform=
ance
>>>> again:
>>>>
>>>> 2) SELECT DISTINCT ON(a) a,b,c,d,e,f ... WHERE somecomplicatedstatemen=
t
>>>>
>>>>
>>>>
>>>>
>>>> Numbers on my machine:
>>>>
>>>> 1) 44000ms
>>>>
>>>> 2) 4300ms
>>>>
>>>> Here's where I read up on the DISTINCT ON syntax:
>>>> http://www.postgresql.org/docs/9.4/static/sql-select.html
>>>>
>>>>
>>>>
>>>> --
>>>> =C3=98yvind Harboe - Can Zylin Consulting help on your project?
>>>> http://www.zylin.com/
>>>
>>>
>>>
>>>
>>> --
>>> =C3=98yvind Harboe - Can Zylin Consulting help on your project?
>>> http://www.zylin.com/
>>> <0001-postgresql-improves-performance-by-taking-advantage-.patch>
>>
>>
>


--=20
=C3=98yvind Harboe - Can Zylin Consulting help on your project?
http://www.zylin.com/