PostgreSQL OR IN ANY

1. create table

postgres=# create table tb5 (id integer,age integer);
CREATE TABLE

postgres=# d tb5
      Table "public.tb5"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 age    | integer | 

postgres=# insert into tb5 select generate_series(1,10),random()*10::integer;
INSERT 0 10

postgres=# select * from tb5;
 id | age 
----+-----
  1 |   6
  2 |   2
  3 |   1
  4 |   7
  5 |   8
  6 |   9
  7 |   0
  8 |   6
  9 |  10
 10 |   0
(10 rows)

postgres=# select * from tb5 where age=1 or age=2 or age=7;
 id | age 
----+-----
  2 |   2
  3 |   1
  4 |   7
(3 rows)

postgres=# explain analyze select * from tb5 where age=1 or age=2 or age=7;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on tb5  (cost=0.00..85.25 rows=64 width=8) (actual time=0.008..0.010 rows=3 loops=1)
   Filter: ((age = 1) OR (age = 2) OR (age = 7))
   Rows Removed by Filter: 7
 Total runtime: 0.024 ms
(4 rows)

postgres=# select * from tb5 where age in(1,2,7);
 id | age 
----+-----
  2 |   2
  3 |   1
  4 |   7
(3 rows)

postgres=# explain analyze select * from tb5 where age in(1,7);
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on tb5  (cost=0.00..69.12 rows=64 width=8) (actual time=0.007..0.008 rows=3 loops=1)
   Filter: (age = ANY ('{1,7}'::integer[]))
   Rows Removed by Filter: 7
 Total runtime: 0.020 ms
(4 rows)

note: the 'OR' operation in explain be changed the 'ANY' operation,It can be seen that postgreSQL handle the 'OR' operation by 'ANY' operation。

postgres=# select * from tb5 where age=ANY('{1,7}');
 id | age 
----+-----
  2 |   2
  3 |   1
  4 |   7
(3 rows)

postgres=# explain analyze select * from tb5 where age=ANY('{1,7}');
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on tb5  (cost=0.00..69.12 rows=64 width=8) (actual time=0.008..0.009 rows=3 loops=1)
   Filter: (age = ANY ('{1,7}'::integer[]))
   Rows Removed by Filter: 7
 Total runtime: 0.020 ms
(4 rows)

ConClusion: The IN operation can be change the OR operation,and the OR operation can be changed the ANY operation. So,when we use the IN or OR operaion,

we can use ANY instead of them.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!