Home Optimize nested sql query
Reply: 3

Optimize nested sql query

Learner
1#
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
2#
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
3#
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
4#
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