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**](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html) This tool, in conjunction with Database SQL Tuning Guide's [*Query Optimizer Concepts*](https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL192) from Oracle Docs should give you a good start. — Rushikumar, Feb 13 '18 at 16:42
@Rushikumar: how would Oracle's tuning guide help with Postgres? — , Feb 13 '18 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](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). — , Feb 13 '18 at 16:52
@a_horse_with_no_name well... there is a way to [configure Oracle SQL Dev for PostgreSQL](http://gokhanatil.com/2014/09/configuring-oracle-sql-developer-for-postgresql.html)... and [Oracel SQL Dev does support connection to PostgreSQL](https://stackoverflow.com/a/22299552/7019742) — Rushikumar, Feb 13 '18 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 — , Feb 13 '18 at 16:57

score 1 · Answer 1 · answered Feb 13 '18 at 16:42

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 · Accepted Answer · 2018-02-14T03:49:52.007

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

score 0 · Answer 3 · answered Feb 13 '18 at 23:25

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

Optimize nested sql query

3 Answers3