CODEWITHSUNDEEP
sqlpostgresqlquery-optimizationgreatest-n-per-group
Learner
Learner

Reputation: 57

Optimize nested sql query

I am trying to optimize the below query which is run every 50 seconds to improve the performance. 

select * from event se1  
where id = (select min(id) from event se2 where
se1.sub_id=se2.sub_id and se2.state = 'PENDING' and se2.il_flag= true)  
and not exists (select id from event se2 where se1.sub_id=se2.sub_id
and se2.state in ('ACCEPTED', 'FAILED', 'INPROCESS'))

Any direction on coming up with a better query to improve its performance ? ( postgres 9.6 ). Appreciate the help.

Event table 

Id              sub_id              state       idl_flag 
1                 23009            Pending        true
2                 23009            Accepted       true
3                 23009            Pending        true
4                 23009            Pending        true
5                 23010            Pending        true
6                 23010            Pending        true
7                 23011            Pending        true
8                 23012            Pending        true

The above table should return 

       5                 23010            Pending       true
       7                 23011            Pending       true

Upvotes: 0

Views: 2086

Answers (3)

Learner
Learner

Reputation: 57

I came up with this query, any suggestion to better the query even further is welcome. 

select se1.* from event se1 join 
  (select sub_id,min(id) as id from event  where state='PENDING' and
           il_flag=false group by sub_id)se2 
   on se1.id=se2.id 
  left join (select sub_id from 
  event se3 where se3.state in ('ACCEPTED', 'FAILED', 'INPROCESS'))se4 
on se1.sub_id=se4.sub_id where se4.sub_id is null

Upvotes: 0

JCMC
JCMC

Reputation: 46

You are doing something different in your answer than you are in your question - there is nothing in the original answer about a special case for the 'Errored' state.

Based on your original question, I've done a little fiddle for you, which doesn't run much faster than your original but I hate nested subqueries. I have factored it all out into a CTE so you can just select the state you want, just to show you a different approach. This may just be personal preference, but IMHO my version is way easier to read than the original!

SQL Fiddle - CTE Example

Upvotes: 0

Kapitula Alexey
Kapitula Alexey

Reputation: 388

You can do a join with event table but using is null condition for this part: 

and not exists (select id from event se2 where se1.sub_id=se2.sub_id
and se2.state in ('ACCEPTED', 'FAILED', 'INPROCESS'))

pelase check How to write "not in ()" sql query using join

But anyway try to avoid using nested queries.

Upvotes: 1

Related Questions