pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Prasanth <dbadmin@nqadmin.com>
To: pgsql-admin@postgresql.org
Subject: using greater than or less than vs equals  in where condition
Date: Thu, 12 May 2005 17:48:10 -0500
Message-ID: <4283DD2A.10007@nqadmin.com> (raw)

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.



view thread (3+ messages)  latest in thread

Message-ID: <4283DD2A.10007@nqadmin.com>
Permalink:  ../4283DD2A.10007@nqadmin.com/
Also on:    postgresql.org/message-id/4283DD2A.10007@nqadmin.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: dbadmin@nqadmin.com
  Subject: Re: using greater than or less than vs equals  in where condition
  In-Reply-To: <4283DD2A.10007@nqadmin.com>
  References: <4283DD2A.10007@nqadmin.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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