X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B8B94D1B8BD for ; Fri, 5 Sep 2003 08:35:04 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 09989-10 for ; Fri, 5 Sep 2003 05:34:56 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 84695D1B8B3 for ; Fri, 5 Sep 2003 05:34:53 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 19vC3S-0002xZ-0V; Fri, 05 Sep 2003 09:34:54 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 9E74316C50; Fri, 5 Sep 2003 09:34:53 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id D34E816B91; Fri, 5 Sep 2003 09:34:52 +0100 (BST) From: Richard Huxton To: rasmus@defero.se, pgsql-performance@postgresql.org Subject: Re: Performance problems on a fairly big table with two key columns. Date: Fri, 5 Sep 2003 09:34:52 +0100 User-Agent: KMail/1.5 References: <1564.62.119.108.236.1062716026.squirrel@www.defero.se> In-Reply-To: <1564.62.119.108.236.1062716026.squirrel@www.defero.se> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200309050934.52082.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/90 X-Sequence-Number: 3456 On Thursday 04 September 2003 23:53, Rasmus Aveskogh wrote: > Hi, > > I have a table that looks like this: > > DATA ID TIME > > |------|----|------| > > The table holds app. 14M rows now and grows by app. 350k rows a day. > > The ID-column holds about 1500 unique values (integer). > The TIME-columns is of type timestamp without timezone. > > I have one index (b-tree) on the ID-column and one index (b-tree) on the > time-column. > > My queries most often look like this: > > SELECT DATA FROM WHERE ID = 1 AND TIME > now() - '1 day'::interval; [snip] > I tried applying a multicolumn index on ID and TIME, but that one won't > even be used (after ANALYZE). The problem is likely to be that the parser isn't spotting that now()-'1 day' is constant. Try an explicit time and see if the index is used. If so, you can write a wrapper function for your expression (mark it STABLE so the planner knows it won't change during the statement). Alternatively, you can do the calculation in the application and use an explicit time. HTH -- Richard Huxton Archonet Ltd