You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2020/08/18 15:40:32 UTC
Cayenne 4.2/Postgres - difference in generated SQL based on nesting
in expressions
Hi all,
I'm currently migrating a large project to Cayenne 4.2. Code wise the transition was easy enough, apart from one thing.
Consider the following two expressions. Both are generated from the same three conditions.
In the first case they're combined using exp1.andExp( exp2 ).andExp( exp3 ).
In the second case, they're combined using:
ObjectSelect
.query( SomeClass.class )
.where( exp1 )
.and( exp2 )
.and( exp3 )
The two different methods yield:
1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>) and (key.number = "161-400") and (date > 2015-08-18)
2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>) and (key.number = "161-400")) and (date > 2015-08-18)
Note the different nesting. In Cayenne 4.1 this was not a problem since the two expressions generated more or less the same SQL.
However in 4.2.M2-SNAPSHOT, the generated SQL is quite different, resulting in a large performance loss in the latter case (as in, queries that previously took a few of milliseconds now take minutes). Here's the SQL generated — note the comparison that happens with the last (date_time) condition in (2):
SQL generated by 1:
- SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1" ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" = "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND ( "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND ( "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40, 3->gl_number:'161-400', 4->date_time:2015-08-18]
SQL generated by 2:
- SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1" ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" = "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND ( "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = ( "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40, 3->gl_number:'161-400', 4->date_time:2015-08-18]
Any ideas?
I'd like to add the disclaimer that I have not been able to identify the exact conditions that result in the generation of the slower SQL, but it certainly does not happen in all cases (i.e. will only happen with some combinations of conditions).
But I'd be happy to try to create a reproducible standalone test-case if required, just give me a shout. I just wanted to start out by throwing this out there.
Cheers,
- hugi
Re: Cayenne 4.2/Postgres - difference in generated SQL based on
nesting in expressions
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Hi Andrus,
turns out it's evaluating each of the conditions in the parentheses as a boolean condition. So together this will filter for results where the conditions are either both true or both false (meaning wrong result).
Somewhat obvious once you spot it but totally wrecked my brain. Fun stuff :)
- hugi
> On 19 Aug 2020, at 06:53, Andrus Adamchik <an...@objectstyle.org> wrote:
>
>> ( "t1"."gl_number" = ? ) ) = ( "t0"."date_time" > ? ) )
>
>
>
> Looks like a bug to me. I've no idea why it even works.
>
> Andrus
>
>
>> On Aug 19, 2020, at 12:30 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> Hi John,
>>
>> the grouping of the expressions (the locations of the parentheses) only relate to the Cayenne expression. The main issue is the different SQL generated by Cayenne 4.2 from those expressions due to the grouping.
>>
>> If you look at the SQL generated for (2), the last condition in the where clause (that uses date_time) is applied with a "=" rather than "AND". It's something I honestly just haven't seen or used before (and I'm embarrassed not knowing how or why it works, reading up right now). But I assume it means it first applies the first condition and then applies the second condition to the result of that to return a final result. In my case, that would make sense performance wise—I have indexes on "date_time" so if date_time is not included in the first query, we're going to have a bad time.
>>
>> What I don't get is why that "=" is generated instead of and "AND". If there's an "AND" the index gets used, regardless of the order of the expressions.
>>
>> - hugi
>>
>>
>>
>>
>>> On 18 Aug 2020, at 18:10, John Huss <jo...@gmail.com> wrote:
>>>
>>> I would tend to think the postgres query planner is smart enough to handle
>>> the differences in the placement of parentheses if that is the only
>>> difference. But in any case, try adding "explain analyze" to the beginning
>>> of the query and then compare the query plans between the fast version and
>>> the slow version. You can post them here too. That would be the best place
>>> to start I think.
>>>
>>> On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>
>>>> Hi all,
>>>> I'm currently migrating a large project to Cayenne 4.2. Code wise the
>>>> transition was easy enough, apart from one thing.
>>>>
>>>> Consider the following two expressions. Both are generated from the same
>>>> three conditions.
>>>>
>>>> In the first case they're combined using exp1.andExp( exp2 ).andExp( exp3
>>>> ).
>>>>
>>>> In the second case, they're combined using:
>>>>
>>>> ObjectSelect
>>>> .query( SomeClass.class )
>>>> .where( exp1 )
>>>> .and( exp2 )
>>>> .and( exp3 )
>>>>
>>>> The two different methods yield:
>>>> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>>>> and (key.number = "161-400") and (date > 2015-08-18)
>>>> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>>>> and (key.number = "161-400")) and (date > 2015-08-18)
>>>>
>>>> Note the different nesting. In Cayenne 4.1 this was not a problem since
>>>> the two expressions generated more or less the same SQL.
>>>>
>>>> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
>>>> resulting in a large performance loss in the latter case (as in, queries
>>>> that previously took a few of milliseconds now take minutes). Here's the
>>>> SQL generated — note the comparison that happens with the last (date_time)
>>>> condition in (2):
>>>>
>>>> SQL generated by 1:
>>>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>>>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>>>> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
>>>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
>>>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>>>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>>>
>>>> SQL generated by 2:
>>>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>>>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>>>> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
>>>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
>>>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>>>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>>>
>>>> Any ideas?
>>>>
>>>> I'd like to add the disclaimer that I have not been able to identify the
>>>> exact conditions that result in the generation of the slower SQL, but it
>>>> certainly does not happen in all cases (i.e. will only happen with some
>>>> combinations of conditions).
>>>>
>>>> But I'd be happy to try to create a reproducible standalone test-case if
>>>> required, just give me a shout. I just wanted to start out by throwing this
>>>> out there.
>>>>
>>>> Cheers,
>>>> - hugi
>>
>
Re: Cayenne 4.2/Postgres - difference in generated SQL based on
nesting in expressions
Posted by Andrus Adamchik <an...@objectstyle.org>.
> ( "t1"."gl_number" = ? ) ) = ( "t0"."date_time" > ? ) )
Looks like a bug to me. I've no idea why it even works.
Andrus
> On Aug 19, 2020, at 12:30 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> Hi John,
>
> the grouping of the expressions (the locations of the parentheses) only relate to the Cayenne expression. The main issue is the different SQL generated by Cayenne 4.2 from those expressions due to the grouping.
>
> If you look at the SQL generated for (2), the last condition in the where clause (that uses date_time) is applied with a "=" rather than "AND". It's something I honestly just haven't seen or used before (and I'm embarrassed not knowing how or why it works, reading up right now). But I assume it means it first applies the first condition and then applies the second condition to the result of that to return a final result. In my case, that would make sense performance wise—I have indexes on "date_time" so if date_time is not included in the first query, we're going to have a bad time.
>
> What I don't get is why that "=" is generated instead of and "AND". If there's an "AND" the index gets used, regardless of the order of the expressions.
>
> - hugi
>
>
>
>
>> On 18 Aug 2020, at 18:10, John Huss <jo...@gmail.com> wrote:
>>
>> I would tend to think the postgres query planner is smart enough to handle
>> the differences in the placement of parentheses if that is the only
>> difference. But in any case, try adding "explain analyze" to the beginning
>> of the query and then compare the query plans between the fast version and
>> the slow version. You can post them here too. That would be the best place
>> to start I think.
>>
>> On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>>> Hi all,
>>> I'm currently migrating a large project to Cayenne 4.2. Code wise the
>>> transition was easy enough, apart from one thing.
>>>
>>> Consider the following two expressions. Both are generated from the same
>>> three conditions.
>>>
>>> In the first case they're combined using exp1.andExp( exp2 ).andExp( exp3
>>> ).
>>>
>>> In the second case, they're combined using:
>>>
>>> ObjectSelect
>>> .query( SomeClass.class )
>>> .where( exp1 )
>>> .and( exp2 )
>>> .and( exp3 )
>>>
>>> The two different methods yield:
>>> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>>> and (key.number = "161-400") and (date > 2015-08-18)
>>> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>>> and (key.number = "161-400")) and (date > 2015-08-18)
>>>
>>> Note the different nesting. In Cayenne 4.1 this was not a problem since
>>> the two expressions generated more or less the same SQL.
>>>
>>> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
>>> resulting in a large performance loss in the latter case (as in, queries
>>> that previously took a few of milliseconds now take minutes). Here's the
>>> SQL generated — note the comparison that happens with the last (date_time)
>>> condition in (2):
>>>
>>> SQL generated by 1:
>>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>>> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
>>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
>>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>>
>>> SQL generated by 2:
>>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>>> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
>>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
>>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>>
>>> Any ideas?
>>>
>>> I'd like to add the disclaimer that I have not been able to identify the
>>> exact conditions that result in the generation of the slower SQL, but it
>>> certainly does not happen in all cases (i.e. will only happen with some
>>> combinations of conditions).
>>>
>>> But I'd be happy to try to create a reproducible standalone test-case if
>>> required, just give me a shout. I just wanted to start out by throwing this
>>> out there.
>>>
>>> Cheers,
>>> - hugi
>
Re: Cayenne 4.2/Postgres - difference in generated SQL based on
nesting in expressions
Posted by John Huss <jo...@gmail.com>.
I see. It’s a good question, but I would just stick with .andExp in that
case. I prefer using that one anyway due to historical usage and also to
keep things consistent if I have Expressions defined apart from queries.
On Tue, Aug 18, 2020 at 4:30 PM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi John,
>
> the grouping of the expressions (the locations of the parentheses) only
> relate to the Cayenne expression. The main issue is the different SQL
> generated by Cayenne 4.2 from those expressions due to the grouping.
>
> If you look at the SQL generated for (2), the last condition in the where
> clause (that uses date_time) is applied with a "=" rather than "AND". It's
> something I honestly just haven't seen or used before (and I'm embarrassed
> not knowing how or why it works, reading up right now). But I assume it
> means it first applies the first condition and then applies the second
> condition to the result of that to return a final result. In my case, that
> would make sense performance wise—I have indexes on "date_time" so if
> date_time is not included in the first query, we're going to have a bad
> time.
>
> What I don't get is why that "=" is generated instead of and "AND". If
> there's an "AND" the index gets used, regardless of the order of the
> expressions.
>
> - hugi
>
>
>
>
> > On 18 Aug 2020, at 18:10, John Huss <jo...@gmail.com> wrote:
> >
> > I would tend to think the postgres query planner is smart enough to
> handle
> > the differences in the placement of parentheses if that is the only
> > difference. But in any case, try adding "explain analyze" to the
> beginning
> > of the query and then compare the query plans between the fast version
> and
> > the slow version. You can post them here too. That would be the best
> place
> > to start I think.
> >
> > On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <hu...@karlmenn.is>
> wrote:
> >
> >> Hi all,
> >> I'm currently migrating a large project to Cayenne 4.2. Code wise the
> >> transition was easy enough, apart from one thing.
> >>
> >> Consider the following two expressions. Both are generated from the same
> >> three conditions.
> >>
> >> In the first case they're combined using exp1.andExp( exp2 ).andExp(
> exp3
> >> ).
> >>
> >> In the second case, they're combined using:
> >>
> >> ObjectSelect
> >> .query( SomeClass.class )
> >> .where( exp1 )
> >> .and( exp2 )
> >> .and( exp3 )
> >>
> >> The two different methods yield:
> >> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> >> and (key.number = "161-400") and (date > 2015-08-18)
> >> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> >> and (key.number = "161-400")) and (date > 2015-08-18)
> >>
> >> Note the different nesting. In Cayenne 4.1 this was not a problem since
> >> the two expressions generated more or less the same SQL.
> >>
> >> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
> >> resulting in a large performance loss in the latter case (as in, queries
> >> that previously took a few of milliseconds now take minutes). Here's the
> >> SQL generated — note the comparison that happens with the last
> (date_time)
> >> condition in (2):
> >>
> >> SQL generated by 1:
> >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key"
> "t1"
> >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> >> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
> >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
> >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> >> 3->gl_number:'161-400', 4->date_time:2015-08-18]
> >>
> >> SQL generated by 2:
> >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key"
> "t1"
> >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> >> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
> >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
> >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> >> 3->gl_number:'161-400', 4->date_time:2015-08-18]
> >>
> >> Any ideas?
> >>
> >> I'd like to add the disclaimer that I have not been able to identify the
> >> exact conditions that result in the generation of the slower SQL, but it
> >> certainly does not happen in all cases (i.e. will only happen with some
> >> combinations of conditions).
> >>
> >> But I'd be happy to try to create a reproducible standalone test-case if
> >> required, just give me a shout. I just wanted to start out by throwing
> this
> >> out there.
> >>
> >> Cheers,
> >> - hugi
>
>
Re: Cayenne 4.2/Postgres - difference in generated SQL based on
nesting in expressions
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Hi John,
the grouping of the expressions (the locations of the parentheses) only relate to the Cayenne expression. The main issue is the different SQL generated by Cayenne 4.2 from those expressions due to the grouping.
If you look at the SQL generated for (2), the last condition in the where clause (that uses date_time) is applied with a "=" rather than "AND". It's something I honestly just haven't seen or used before (and I'm embarrassed not knowing how or why it works, reading up right now). But I assume it means it first applies the first condition and then applies the second condition to the result of that to return a final result. In my case, that would make sense performance wise—I have indexes on "date_time" so if date_time is not included in the first query, we're going to have a bad time.
What I don't get is why that "=" is generated instead of and "AND". If there's an "AND" the index gets used, regardless of the order of the expressions.
- hugi
> On 18 Aug 2020, at 18:10, John Huss <jo...@gmail.com> wrote:
>
> I would tend to think the postgres query planner is smart enough to handle
> the differences in the placement of parentheses if that is the only
> difference. But in any case, try adding "explain analyze" to the beginning
> of the query and then compare the query plans between the fast version and
> the slow version. You can post them here too. That would be the best place
> to start I think.
>
> On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Hi all,
>> I'm currently migrating a large project to Cayenne 4.2. Code wise the
>> transition was easy enough, apart from one thing.
>>
>> Consider the following two expressions. Both are generated from the same
>> three conditions.
>>
>> In the first case they're combined using exp1.andExp( exp2 ).andExp( exp3
>> ).
>>
>> In the second case, they're combined using:
>>
>> ObjectSelect
>> .query( SomeClass.class )
>> .where( exp1 )
>> .and( exp2 )
>> .and( exp3 )
>>
>> The two different methods yield:
>> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>> and (key.number = "161-400") and (date > 2015-08-18)
>> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
>> and (key.number = "161-400")) and (date > 2015-08-18)
>>
>> Note the different nesting. In Cayenne 4.1 this was not a problem since
>> the two expressions generated more or less the same SQL.
>>
>> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
>> resulting in a large performance loss in the latter case (as in, queries
>> that previously took a few of milliseconds now take minutes). Here's the
>> SQL generated — note the comparison that happens with the last (date_time)
>> condition in (2):
>>
>> SQL generated by 1:
>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>
>> SQL generated by 2:
>> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
>> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
>> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
>> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
>> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
>> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>>
>> Any ideas?
>>
>> I'd like to add the disclaimer that I have not been able to identify the
>> exact conditions that result in the generation of the slower SQL, but it
>> certainly does not happen in all cases (i.e. will only happen with some
>> combinations of conditions).
>>
>> But I'd be happy to try to create a reproducible standalone test-case if
>> required, just give me a shout. I just wanted to start out by throwing this
>> out there.
>>
>> Cheers,
>> - hugi
Re: Cayenne 4.2/Postgres - difference in generated SQL based on
nesting in expressions
Posted by John Huss <jo...@gmail.com>.
I would tend to think the postgres query planner is smart enough to handle
the differences in the placement of parentheses if that is the only
difference. But in any case, try adding "explain analyze" to the beginning
of the query and then compare the query plans between the fast version and
the slow version. You can post them here too. That would be the best place
to start I think.
On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi all,
> I'm currently migrating a large project to Cayenne 4.2. Code wise the
> transition was easy enough, apart from one thing.
>
> Consider the following two expressions. Both are generated from the same
> three conditions.
>
> In the first case they're combined using exp1.andExp( exp2 ).andExp( exp3
> ).
>
> In the second case, they're combined using:
>
> ObjectSelect
> .query( SomeClass.class )
> .where( exp1 )
> .and( exp2 )
> .and( exp3 )
>
> The two different methods yield:
> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> and (key.number = "161-400") and (date > 2015-08-18)
> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> and (key.number = "161-400")) and (date > 2015-08-18)
>
> Note the different nesting. In Cayenne 4.1 this was not a problem since
> the two expressions generated more or less the same SQL.
>
> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
> resulting in a large performance loss in the latter case (as in, queries
> that previously took a few of milliseconds now take minutes). Here's the
> SQL generated — note the comparison that happens with the last (date_time)
> condition in (2):
>
> SQL generated by 1:
> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>
> SQL generated by 2:
> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key" "t1"
> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> 3->gl_number:'161-400', 4->date_time:2015-08-18]
>
> Any ideas?
>
> I'd like to add the disclaimer that I have not been able to identify the
> exact conditions that result in the generation of the slower SQL, but it
> certainly does not happen in all cases (i.e. will only happen with some
> combinations of conditions).
>
> But I'd be happy to try to create a reproducible standalone test-case if
> required, just give me a shout. I just wanted to start out by throwing this
> out there.
>
> Cheers,
> - hugi