Distinct query causing the query to be slow

Question

I have a postgres query which is almost taking 7 seconds. Joining two tables with where clause and when i use distinct it takes 7 seconds and without distinct i get the result in 500ms. I even applied index but of no help. How can i tune the query for better perfomance

select distinct RES.* from ACT_RU_TASK RES inner join ACT_RU_IDENTITYLINK I on 
I.TASK_ID_ = RES.ID_ WHERE RES.ASSIGNEE_ is null 
and I.TYPE_ = 'candidate'and ( I.GROUP_ID_ IN ( 'us1','us2') )
order by RES.priority_ desc LIMIT 10 OFFSET 0

For every RES.ID_ i have two I.TASK_ID_ so i need only unique records

https://stackoverflow.com/questions/7943957/huge-performance-difference-when-using-group-by-vs-distinct check this link may help you — Jay Shankar Gupta, Apr 09 '18 at 10:43

score 3 · Answer 1 · answered Apr 09 '18 at 10:42

Instead of using distinct use exists:

select RES.*
from ACT_RU_TASK RES 
where exists (select 1
              from ACT_RU_IDENTITYLINK I 
              where I.TASK_ID_ = RES.ID_ and
                    I.TYPE_ = 'candidate' and 
                    I.GROUP_ID_ IN ( 'us1','us2') 
             ) and
      RES.ASSIGNEE_ is null 
order by RES.priority_ desc
LIMIT 10 OFFSET 0;

For this query, you want an index on ACT_RU_IDENTITYLINK(TASK_ID_, TYPE_, GROUP_ID_). It is also possible that an index on ACT_RU_TASK(ASSIGNEE_, priority_, ID_) could be used.

Distinct query causing the query to be slow

1 Answers1