using greater than or less than vs equals in where condition

Поиск
Список
Период
Сортировка
От Prasanth
Тема using greater than or less than vs equals in where condition
Дата
Msg-id 4283DD2A.10007@nqadmin.com
обсуждение исходный текст
Ответы Re: using greater than or less than vs equals in where condition
Список pgsql-admin
PG Version: 7.4.7
OS: RedHat FC3


Below are two queries that would give the same results but amount of execution
time is so different.

explain analyze select * from data where type_code >'2' AND type_code<'4';
                                                        QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using data_type_code_idx on data  (cost=0.00..3.02 rows=1 width=36)
(actual time=875.218..875.440 rows=46 loops=1)
   Index Cond: ((type_code > 2::smallint) AND (type_code < 4::smallint))
 Total runtime: 875.503 ms
(3 rows)


explain analyze select * from data where type_code = '3';
                                                       QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using data_type_code_idx on data  (cost=0.00..65.79 rows=2152
width=36) (actual time=15.925..15.980 rows=46 loops=1)
   Index Cond: (type_code = 3::smallint)
 Total runtime: 16.037 ms
(3 rows)


It can be said that first has two where conditions where second one has just
one. In that case just the below one.

explain analyze select * from data where type_code ='3' OR type_code='5' OR
type_code='4';

                                                           QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using data_type_code_idx, data_type_code_idx, data_type_code_idx on
data  (cost=0.00..229.66 rows=6454 width=36) (actual time=0.029..1.773 rows=1210
loops=1)
   Index Cond: ((type_code = 3::smallint) OR (type_code = 5::smallint) OR
(type_code = 4::smallint))
 Total runtime: 2.115 ms
(3 rows)

explain analyze select * from data where type_code >'2' AND type_code<='5';
                                                         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using data_type_code_idx on data  (cost=0.00..3.02 rows=1 width=36)
(actual time=2193.622..2197.286 rows=1210 loops=1)
   Index Cond: ((type_code > 2::smallint) AND (type_code <= 5::smallint))
 Total runtime: 2197.584 ms
(3 rows)


Is it inherently bad to use ">" or "<"  while you can use equals?

Thanks,
-Prasanth.

В списке pgsql-admin по дате отправления:

Предыдущее
От: Marko Ristola
Дата:
Сообщение: Re: [ODBC] catastrophic error
Следующее
От: Tom Lane
Дата:
Сообщение: Re: using greater than or less than vs equals in where condition