Обсуждение: using greater than or less than vs equals in where condition

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.

Prasanth <dbadmin@nqadmin.com> writes:
> PG Version: 7.4.7

> explain analyze select * from data where type_code >'2' AND type_code<'4';
> [ is slow ]
> explain analyze select * from data where type_code = '3';
> [ isn't ]

Are there a whole lot of rows with type_code = 2?  If so, this is
fixed in 8.0.

2003-12-20 20:23  tgl

    * src/: backend/access/nbtree/nbtinsert.c,
    backend/access/nbtree/nbtpage.c, backend/access/nbtree/nbtsearch.c,
    include/access/nbtree.h: Improve btree's
    initial-positioning-strategy code so that we never need to step
    more than one entry after descending the search tree to arrive at
    the correct place to start the scan.  This can improve the behavior
    substantially when there are many entries equal to the chosen
    boundary value.  Per suggestion from Dmitry Tkach, 14-Jul-03.

            regards, tom lane

Yes most of the data has type_code =2 probably 99.9%.

Thanks,
-Prasanth.

Tom Lane wrote:
> Prasanth <dbadmin@nqadmin.com> writes:
>
>>PG Version: 7.4.7
>
>
>>explain analyze select * from data where type_code >'2' AND type_code<'4';
>>[ is slow ]
>>explain analyze select * from data where type_code = '3';
>>[ isn't ]
>
>
> Are there a whole lot of rows with type_code = 2?  If so, this is
> fixed in 8.0.
>
> 2003-12-20 20:23  tgl
>
>     * src/: backend/access/nbtree/nbtinsert.c,
>     backend/access/nbtree/nbtpage.c, backend/access/nbtree/nbtsearch.c,
>     include/access/nbtree.h: Improve btree's
>     initial-positioning-strategy code so that we never need to step
>     more than one entry after descending the search tree to arrive at
>     the correct place to start the scan.  This can improve the behavior
>     substantially when there are many entries equal to the chosen
>     boundary value.  Per suggestion from Dmitry Tkach, 14-Jul-03.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>