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, Øyvind Harboe <[email protected]> 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 numerous > 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 performance > again: > > 2) SELECT DISTINCT ON(a) a,b,c,d,e,f ... WHERE somecomplicatedstatement > > > > > 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 > > > > -- > Øyvind Harboe - Can Zylin Consulting help on your project? > http://www.zylin.com/
