Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e6VEnfB63337 for ; Mon, 31 Jul 2000 10:49:42 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA09452; Mon, 31 Jul 2000 10:49:20 -0400 (EDT) To: Itai Zukerman cc: pgsql-sql@postgresql.org Subject: Re: Indices and time spans In-reply-to: <87g0oqbej1.fsf@matt.w80.math-hat.com> References: <87g0oqbej1.fsf@matt.w80.math-hat.com> Comments: In-reply-to Itai Zukerman message dated "31 Jul 2000 09:44:18 -0400" Date: Mon, 31 Jul 2000 10:49:20 -0400 Message-ID: <9449.965054960@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/285 Itai Zukerman writes: > I have this: > SELECT ... > FROM trade, entry > WHERE AGE( CURRENT_TIMESTAMP, posted ) <= '5 minutes'::TIMESPAN > AND trade.entryId = entry.entryId > That is, fetch all trades executed in the last 5 minutes. > This query seems to run pretty slowly when trade is filled. Putting > an index on trade ( posted ) doesn't seem to help any No, it wouldn't, since the indexscan machinery can only deal with WHERE clauses that look like "indexed_column relational_op constant". You need to recast the clause as something like WHERE posted >= (CURRENT_TIMESTAMP - '5 minutes'::TIMESPAN) Then you still have the problem of persuading Postgres that it should treat the right side of this as a constant and not something to re-evaluate at each row. In 7.0 and later it's possible to do that with creative use of a user-defined function marked "iscachable" (for details see the archives for the last time this question came up, a few months back). But the lowest-tech solution may be to calculate the cutoff time on the application side, so you can just send it as a constant to begin with. regards, tom lane