X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 65FFBD1B837 for ; Wed, 4 Feb 2004 23:22:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 07338-01 for ; Wed, 4 Feb 2004 19:22:26 -0400 (AST) Received: from mail3.worldispnetwork.com (linux403.worldispnetwork.com [66.132.146.16]) by svr1.postgresql.org (Postfix) with SMTP id 82336D1B52F for ; Wed, 4 Feb 2004 19:22:24 -0400 (AST) Received: (qmail 15355 invoked by uid 399); 4 Feb 2004 23:20:45 -0000 Received: from unknown (HELO px.nrglinx.local) (postmaster@linxtechnologies.com@65.124.40.63) by linux403.worldispnetwork.com with SMTP; 4 Feb 2004 23:20:45 -0000 Received: from [192.168.0.65] (helo=nrglinxmain.nrglinx.local) by px.nrglinx.local with esmtp (Exim 3.35 #1 (Debian)) id 1AoWLf-00005n-00 for ; Wed, 04 Feb 2004 15:22:23 -0800 Received: from harvey.nrglinx.local ([192.168.0.133]) by nrglinxmain.nrglinx.local with Microsoft SMTPSVC(5.0.2195.6713); Wed, 4 Feb 2004 15:22:23 -0800 Subject: Re: select is not using index? From: Corey Edwards To: Mark Harrison Cc: pgsql-performance@postgresql.org In-Reply-To: <40217853.20309@pixar.com> References: <40217853.20309@pixar.com> Content-Type: text/plain Organization: Linx Technologies Message-Id: <1075936942.12400.18.camel@harvey> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Wed, 04 Feb 2004 15:22:23 -0800 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 04 Feb 2004 23:22:23.0531 (UTC) FILETIME=[BE7167B0:01C3EB75] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/66 X-Sequence-Number: 5590 On Wed, 2004-02-04 at 14:55, Mark Harrison wrote: > testdb=# \d bigtable > Table "public.bigtable" > Column | Type | Modifiers > ---------+---------+----------- > id | bigint | not null > typeid | integer | not null > reposid | integer | not null > Indexes: bigtable_id_key unique btree (id) > testdb=# explain select * from bigtable where id = 123; Your column is a bigint but 123 defaults to type int. Indexes aren't used when there's a type mismatch. Use an explicit cast or quote it: select * from bigtable where id = 123::bigint; Or select * from bigtable where id = '123'; Corey