Hi,
The query doesn't use index.
Did you correctly apply my changes to indexes in the model ?
Here my output using similar cardinalities (100k activities, 1M roles):
Query:
EXPLAIN ANALYZE SELECT activity0._VAL , activityuseraccountrole0 ._VAL
FROM "activity".ACTIVITY activity0 LEFT OUTER JOIN
"activityuseraccountrole".ACTIVITYUSERACCOUNTROLE activityuseraccountrole0
ON activity0.activityId=activityuseraccountrole0.activityId;
Output:
SELECT
ACTIVITY0._VAL,
ACTIVITYUSERACCOUNTROLE0._VAL
FROM "activity".ACTIVITY ACTIVITY0
/* "activity".ACTIVITY.__SCAN_ */
/* scanCount: 100001 */
LEFT OUTER JOIN "activityuseraccountrole".ACTIVITYUSERACCOUNTROLE
ACTIVITYUSERACCOUNTROLE0
/* "activityuseraccountrole"."Activityuseraccountrole_idx": ACTIVITYID
= ACTIVITY0.ACTIVITYID */
ON ACTIVITY0.ACTIVITYID = ACTIVITYUSERACCOUNTROLE0.ACTIVITYID
/* scanCount: 1100000 */
(1 row, 1190 ms)
2016-05-27 9:23 GMT+03:00 jan.swaelens <[email protected]>:
> Hello,
>
> This one has been running for 10 minutes now without producing results - so
> rather the join.
>
> /EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
> LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
> ON activity0.activity_Id=activityuseraccountrole0.activity_Id/
>
> This one works fine though:
>
> /EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0,
> "Activity".activityuseraccountrole activityuseraccountrole0
> WHERE activity0.activity_Id=activityuseraccountrole0.activity_Id/
>
> /SELECT DISTINCT
> ACTIVITY0._KEY,
> ACTIVITY0._VAL,
> ACTIVITY0.ACTIVITY_ID,
> ACTIVITY0.TIMESTAMP,
> ACTIVITY0.CONTAINER_ID,
> ACTIVITY0.ACTIVITYTYPE_ID,
> ACTIVITY0.REALIZATION_ID,
> ACTIVITY0.KERNEL_ID,
> ACTIVITY0.PREDECESSORTYPE_ENUMID,
> ACTIVITY0.SUCCESSORTYPE_ENUMID,
> ACTIVITY0.DURATIONUNIT_ENUMID,
> ACTIVITY0.NAME,
> ACTIVITY0.NAME_MLID,
> ACTIVITY0.DESCRIPTION,
> ACTIVITY0.DESCRIPTION_MLID,
> ACTIVITY0.DURATION,
> ACTIVITY0.REQUIRED,
> ACTIVITY0.ESTIMSTARTDATE,
> ACTIVITY0.ESTIMSTARTHOUR,
> ACTIVITY0.ESTIMENDHOUR,
> ACTIVITY0.ESTIMENDDATE,
> ACTIVITY0.REMOVEFROMWORKLIST,
> ACTIVITY0.SEQUENCENR,
> ACTIVITY0.SESSION_ID,
> ACTIVITY0.LASTACTIVITY_ID,
> ACTIVITY0.SYSREPOPERATION_ID,
> ACTIVITY0.LIFECYCLEREPORTING,
> ACTIVITY0.DUEDATE,
> ACTIVITY0.PRIORITY_ENUMID,
> ACTIVITY0.NOTIFY,
> ACTIVITYUSERACCOUNTROLE0._KEY,
> ACTIVITYUSERACCOUNTROLE0._VAL,
> ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID,
> ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID
> FROM "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
> /* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
> /* scanCount: 1027840 */
> INNER JOIN "Activity".ACTIVITY ACTIVITY0
> /* "Activity".PK_ACTIVITY: ACTIVITY_ID =
> ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID */
> ON 1=1
> /* scanCount: 2055678 */
> WHERE ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID/
>
> So looks like the LEFT OUTER is the culprit, or at least one of them.
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5267.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
