cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Problem with catastrophic performance degradation under PostgreSQL
Date Mon, 20 Apr 2015 16:07:27 GMT
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 <oyvind.harboe@zylin.com> 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/


Mime
View raw message