Optimize nested sql query

Question

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

Take a look at Oracle SQL Developer This tool, in conjunction with Database SQL Tuning Guide's Query Optimizer Concepts from Oracle Docs should give you a good start. — Rushikumar
– Rushikumar, Commented Feb 13, 2018 at 16:42
@Rushikumar: how would Oracle's tuning guide help with Postgres? — user330315
– user330315, Commented Feb 13, 2018 at 16:47
It's not entirely clear to me what your statement should achieve. Can you please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. — user330315
– user330315, Commented Feb 13, 2018 at 16:52
@a_horse_with_no_name well... there is a way to configure Oracle SQL Dev for PostgreSQL... and Oracel SQL Dev does support connection to PostgreSQL — Rushikumar
– Rushikumar, Commented Feb 13, 2018 at 16:52
True, you can use SQL Developer to connect to a Postgres server. But that doesn't meant the strategies to tune a query are the same as for Oracle. The Oracle tuning guide is pretty much useless for Postgres — user330315
– user330315, Commented Feb 13, 2018 at 16:57

Kapitula Alexey · Accepted Answer · 2018-02-13 16:42:30Z

1

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.

answered Feb 13, 2018 at 16:42

Kapitula Alexey

3884 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JCMC · Accepted Answer · 2018-02-13 23:25:08Z

0

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

answered Feb 13, 2018 at 23:25

JCMC

464 bronze badges

Comments

Learner · Accepted Answer · 2018-02-14 03:49:52Z

0

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

edited Feb 14, 2018 at 3:49

answered Feb 13, 2018 at 23:09

Learner

572 silver badges9 bronze badges

Collectives™ on Stack Overflow

Optimize nested sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related