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 <[email protected]> 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
