From: | Prasanth <dbadmin(at)nqadmin(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | using greater than or less than vs equals in where condition |
Date: | 2005-05-12 22:48:10 |
Message-ID: | 4283DD2A.10007@nqadmin.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-05-13 03:12:25 | Re: using greater than or less than vs equals in where condition |
Previous Message | Marko Ristola | 2005-05-12 21:58:03 | Re: [ODBC] catastrophic error |