cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mirko Teran (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CAY-2005) Improving generated DISTINCT performance on PostgreSQL
Date Wed, 29 Apr 2015 07:41:06 GMT

    [ https://issues.apache.org/jira/browse/CAY-2005?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14518909#comment-14518909
] 

Mirko Teran commented on CAY-2005:
----------------------------------

I have a bit of an issue with this solution.

Query:

{quote}
final SelectQuery<Account> query = SelectQuery.query(Account.class);
query.andQualifier(Account.WORKSPACE_ACCOUNTS.dot(WorkspaceAccount.WORKSPACE).eq(ws));
query.addOrdering(Account.USERNAME.asc());
query.addOrdering(Account.ID.asc());
{quote}

SQL generated by M2 works:
{quote}
SELECT 
DISTINCT t0.active, t0.created, t0.email, t0.expiration_date, t0.first_name, t0.hash_iterations,
t0.id, t0.last_name, t0.password, t0.role, t0.salt, t0.type, t0.username, t0.verified 
FROM public.account t0 
JOIN public.workspace_account t1 ON (t0.id = t1.account_id) 
WHERE t1.workspace_id = 2 
ORDER BY t0.username, t0.id
{quote}

SQL generated by building from current source (M3) does not:
{quote}
SELECT 
DISTINCT ON (t0.id) t0.active, t0.created, t0.email, t0.expiration_date, t0.first_name, t0.hash_iterations,
t0.id, t0.last_name, t0.password, t0.role, t0.salt, t0.type, t0.username, t0.verified 
FROM public.account t0 
JOIN public.workspace_account t1 ON (t0.id = t1.account_id) 
WHERE t1.workspace_id = 2 
ORDER BY t0.username, t0.id 
{quote}

Error: _SELECT DISTINCT ON expressions must match initial ORDER BY expressions._

I'm guessing ordering need to be addressed in 'DISTINCT ON' part in this case.


> Improving generated DISTINCT performance on PostgreSQL
> ------------------------------------------------------
>
>                 Key: CAY-2005
>                 URL: https://issues.apache.org/jira/browse/CAY-2005
>             Project: Cayenne
>          Issue Type: Improvement
>            Reporter: Andrus Adamchik
>            Assignee: Andrus Adamchik
>             Fix For: 4.0.M3
>
>
> The problem is described here: http://markmail.org/message/j7alu33vfm7rjeba  Auto-generated
DISTINCT keyword when translating SelectQuery results in slowdown of PostgreSQL queries (won't
be surprised if this affects other DBs as well). Alternative PostgreSQL syntax "DISTINCT ON(id1,
id2, ..)" solves the issue.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message