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
