Re: Problem with catastrophic performance degradation under PostgreSQL

[Øyvind Harboe]

Hi Andrus,

here's my stab at it. I'm sure it's all sorts of wrong :-) but the
pathological performance drop on PostgreSQL is gone.

I actually tested this on Cayenne 3 and then copied it over to a patch
on origin/master.


---------- Forwarded message ----------
From: Andrus Adamchik <and...@objectstyle.org>
Date: Mon, Apr 20, 2015 at 6:07 PM
Subject: Re: Problem with catastrophic performance degradation under PostgreSQL
To: dev@cayenne.apache.org


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.har...@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/




-- 
Øyvind Harboe - Can Zylin Consulting help on your project?
http://www.zylin.com/

From 94a028569f5705b197a2f223398e49e0869f3447 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=98yvind=20Harboe?= <oyvind.har...@zylin.com>
Date: Mon, 20 Apr 2015 19:36:46 +0200
Subject: [PATCH] postgresql: improves performance by taking advantage of the
 DISTINCT ON dialect

---
 .../access/translator/select/SelectTranslator.java | 14 +++++++++--
 .../dba/postgres/PostgresSelectTranslator.java     | 29 ++++++++++++++++++++++
 2 files changed, 41 insertions(+), 2 deletions(-)

diff --git a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
index 4c0aa4d..aa48de0 100644
--- a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
+++ b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
@@ -156,8 +156,8 @@ public class SelectTranslator extends QueryAssembler {
                 }
             }
 
-            if (!suppressingDistinct) {
-                queryBuf.append("DISTINCT ");
+            if (!suppressingDistinct)  {
+                queryBuf.append(buildDistinctStatement() + " ");
             }
         }
 
@@ -230,6 +230,16 @@ public class SelectTranslator extends QueryAssembler {
     }
 
     /**
+     * Allows subclasses to insert their own dialect of DISTINCT
+     * statement to improve performance.
+     *
+     * @return string representing the DISTINCT statement
+     */
+    protected String buildDistinctStatement() {
+        return "DISTINCT";
+    }
+
+    /**
      * Handles appending optional limit and offset clauses. This implementation
      * does nothing, deferring to subclasses to define the LIMIT/OFFSET clause
      * syntax.
diff --git a/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java b/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
index 3344e25..7eed165 100644
--- a/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
+++ b/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
@@ -53,4 +53,33 @@ class PostgresSelectTranslator extends SelectTranslator {
         }
     }
 
+    @Override
+    protected String buildDistinctStatement() {
+        StringBuilder builder = new StringBuilder();
+        builder.append("DISTINCT ON (");
+
+        DbEntity table = getRootDbEntity();
+        boolean addComma = false;
+        for (final DbAttribute dba : table.getPrimaryKeys()) {
+            if (!dba.isPrimaryKey()) {
+                continue;
+            }
+
+            for (ColumnDescriptor column : getResultColumns()) {
+                if (column.getTableName().equals(table.getName())) {
+                    if (column.getName().equals(dba.getName())) {
+                        if (addComma) {
+                            builder.append(", ");
+                        }
+                        builder.append(column.getQualifiedColumnName());
+                        addComma = true;
+                        break;
+                    }
+                }
+            }
+        }
+        builder.append(")");
+        return builder.toString();
+    }
+
 }
-- 
1.9.1