X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AE7C852A60 for ; Thu, 12 May 2005 19:44:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05679-07 for ; Thu, 12 May 2005 22:44:51 +0000 (GMT) Received: from mail.liquidcore.com (mail.liquidcore.net [68.17.96.60]) by svr1.postgresql.org (Postfix) with ESMTP id 964675294D for ; Thu, 12 May 2005 19:44:49 -0300 (ADT) Received: (from root@localhost) by mail.liquidcore.com (8.13.1/8.12.10) id j4CMj4T6022097 for pgsql-admin@postgresql.org; Thu, 12 May 2005 17:45:04 -0500 Received: from [192.168.0.81] (ppasalalinux.nqadmin.com [192.168.0.81]) by mail.liquidcore.com (8.13.1/8.12.10) with ESMTP id j4CMj3fg022062 for ; Thu, 12 May 2005 17:45:03 -0500 Message-ID: <4283DD2A.10007@nqadmin.com> Date: Thu, 12 May 2005 17:48:10 -0500 From: Prasanth Reply-To: dbadmin@nqadmin.com User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: using greater than or less than vs equals in where condition X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-scanner: scanned for malicious content X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.051 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/176 X-Sequence-Number: 17605 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.