Home Optimize nested sql query
Reply: 3

Optimize nested sql query

Learner Published in 2018-02-13 16:33:02Z

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
Kapitula Alexey
Kapitula Alexey Reply to 2018-02-13 16:42:30Z

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.

Learner Reply to 2018-02-14 03:49:52Z

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
JCMC Reply to 2018-02-13 23:25:08Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.457549 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO