cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øyvind Harboe <oyvind.har...@zylin.com>
Subject Problem with catastrophic performance degradation under PostgreSQL
Date Mon, 20 Apr 2015 15:09:57 GMT
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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message