X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D207F3A3BD9 for ; Fri, 5 Nov 2004 08:34:16 +0000 (GMT) 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 03672-09 for ; Fri, 5 Nov 2004 08:34:14 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 367643A3B54 for ; Fri, 5 Nov 2004 08:34:14 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iA58Y2GR014746 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 5 Nov 2004 01:34:04 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iA58Y2V3045072; Fri, 5 Nov 2004 01:34:02 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iA58Y1xn045071; Fri, 5 Nov 2004 01:34:01 -0700 (MST) (envelope-from mfuhr) Date: Fri, 5 Nov 2004 01:34:01 -0700 From: Michael Fuhr To: Antony Paul Cc: pgsql-performance@postgresql.org Subject: Re: Checking = with timestamp field is slow Message-ID: <20041105083401.GA44953@winnie.fuhr.org> References: <2989532e0411042316566b35f4@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2989532e0411042316566b35f4@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/89 X-Sequence-Number: 9043 On Fri, Nov 05, 2004 at 12:46:20PM +0530, Antony Paul wrote: > I have a table which have more than 200000 records. I need to get > the records which matches like this > > where today::date = '2004-11-05'; > > This is the only condition in the query. There is a btree index on the > column today. Is there any way to optimise it. Is the today column a TIMESTAMP as the subject implies? If so then your queries probably aren't using the index because you're changing the type to something that's not indexed. Your queries should speed up if you create an index on DATE(today): CREATE INDEX foo_date_today_idx ON foo (DATE(today)); After creating the new index, use WHERE DATE(today) = '2004-11-05' in your queries. EXPLAIN ANALYZE should show that the index is being used. -- Michael Fuhr http://www.fuhr.org/~mfuhr/