BUG #13112: Catastrophic performance degradation without DISTINCT ON statement

The following bug has been logged on the website:

Bug reference:      13112
Logged by:          Øyvind Harboe
Email address:      oyvind.harboe@zylin.com
PostgreSQL version: 9.1.0
Operating system:   Ubuntu
Description:

I've been testing out PostgreSQL vs. Derby/MS SQL 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 Apache Cayenne generates.

Apache 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