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 7E51F5E3F15 for ; Sun, 29 Aug 2004 15:27:21 -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 17617-04 for ; Sun, 29 Aug 2004 18:27:18 +0000 (GMT) Received: from mpls-qmqp-04.inet.qwest.net (mpls-qmqp-04.inet.qwest.net [63.231.195.115]) by svr1.postgresql.org (Postfix) with SMTP id CC9C15E3631 for ; Sun, 29 Aug 2004 15:27:15 -0300 (ADT) Received: (qmail 18210 invoked by uid 0); 29 Aug 2004 18:27:11 -0000 Received: from mpls-pop-04.inet.qwest.net (63.231.195.4) by mpls-qmqp-04.inet.qwest.net with QMQP; 29 Aug 2004 18:27:11 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-04.inet.qwest.net with SMTP; 29 Aug 2004 18:27:11 -0000 Date: Sun, 29 Aug 2004 12:28:48 -0600 Message-Id: <1093804128.5493.22.camel@localhost.localdomain> From: "Scott Marlowe" To: "Jack Kerkhof" Cc: pgsql-performance@postgresql.org Subject: Re: Why does a simple query not use an obvious index? In-Reply-To: References: Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit 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: 200408/425 X-Sequence-Number: 8061 On Fri, 2004-08-27 at 11:12, Jack Kerkhof wrote: > The query: > > select count(*) from billing where timestamp > now()-60 > > should obviously use the index > > CREATE INDEX billing_timestamp_idx ON billing USING btree > ("timestamp" timestamp_ops); > > on a table with 1400000 rows. > > But it uses a Seq Scan. If I set enable_seqscan=no, it indicates a > queryplan could not be calculated. Have you tried this: marlowe=> select now()-60; ERROR: operator does not exist: timestamp with time zone - integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. you likely need: smarlowe=> select now()-'60 seconds'::interval; ?column? ------------------------------- 2004-08-29 12:25:38.249564-06 inside there. Also, count(*) is likely to always generate a seq scan due to the way aggregates are implemented currently in pgsql. you might want to try: select somefield from sometable where timestampfield > now()-'60 seconds'::interval and count the number of returned rows. If there's a lot, it won't be any faster, if there's a few, it should be a win.