pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedusing greater than or less than vs equals in where condition
3+ messages / 2 participants
[nested] [flat]
* using greater than or less than vs equals in where condition
@ 2005-05-12 22:48 Prasanth <dbadmin@nqadmin.com>
2005-05-13 03:12 ` Re: using greater than or less than vs equals in where condition Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Prasanth @ 2005-05-12 22:48 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using greater than or less than vs equals in where condition
2005-05-12 22:48 using greater than or less than vs equals in where condition Prasanth <dbadmin@nqadmin.com>
@ 2005-05-13 03:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 15:27 ` Re: using greater than or less than vs equals in where condition Prasanth <dbadmin@nqadmin.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-05-13 03:12 UTC (permalink / raw)
To: dbadmin@nqadmin.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using greater than or less than vs equals in where condition
2005-05-12 22:48 using greater than or less than vs equals in where condition Prasanth <dbadmin@nqadmin.com>
2005-05-13 03:12 ` Re: using greater than or less than vs equals in where condition Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-13 15:27 ` Prasanth <dbadmin@nqadmin.com>
0 siblings, 0 replies; 3+ messages in thread
From: Prasanth @ 2005-05-13 15:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-13 15:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-12 22:48 using greater than or less than vs equals in where condition Prasanth <dbadmin@nqadmin.com>
2005-05-13 03:12 ` Re: using greater than or less than vs equals in where condition Tom Lane <tgl@sss.pgh.pa.us>
2005-05-13 15:27 ` Re: using greater than or less than vs equals in where condition Prasanth <dbadmin@nqadmin.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox