2

I have query with an IN clause. 

select * from student where id in (1, 2, 3, 4, 5)

How will the engine parsed and execute this SQL query?
Is it like this, parsed in five different queries or something else?

select * from student where id = 1
select * from student where id = 2
select * from student where id = 3
select * from student where id = 4
select * from student where id = 5
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Vijay Thayalan
  • 83
  • 6

2 Answers2

3

No, it will not do that although the five queries and the one query will return the same result (assuming no modifications to the table while running five queries). The five queries would require scanning the student table five times, once for each query. The "scan" might actually use an index an be really fast.

The five queries also need to be compiled and executed, which add additional overhead.

With the in, you are executing one query. Without an index, Postgres will look at each row to see if it matches. If so, it goes in the result set. With an index, it will just look up the appropriate rows in the index.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I did not get you Gordan. Are you saying that the 'IN' condition will not run as separate queries? If we have two 'IN' condition in a single query with two different tables, will that run two single query? Like one query for first table and another query for second table. Please give your comments :) – Vijay Thayalan Mar 12 '15 at 14:05
  • 2
    @VijayThayalan: one query is executed as one query regardless of the conditions. –  Mar 12 '15 at 15:46
3

The Postgres query planner translates IN with a list (row) expression:

select * from student where id in (1, 2, 3, 4, 5);

exactly like this:

select * from student where id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5;

You can see this if you run the statement with EXPLAIN.

Note that there are two basic forms of IN. Details in this related answer on dba.SE:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin for your valuable comments. Here i have one more suggestions, whether two many in conditions will effect performance? If so, whether spiting queries will help us to improve performance? Please leave me your valuable suggestions – Vijay Thayalan Mar 13 '15 at 08:47
  • @VijayThayalan: There is [advice for better performance in answer I inked to](http://dba.stackexchange.com/a/91539/3684). Similar [here](http://dba.stackexchange.com/questions/61520/how-to-do-where-x-in-val1-val2-in-plpgsql/61552#61552) – Erwin Brandstetter Mar 13 '15 at 14:26