> ( "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 <h...@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 <johnth...@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 <h...@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 >
