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/
