Обсуждение: Indices and time spans
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 (the same query plan is generated). Any suggestions? -itai
Itai Zukerman <zukerman@math-hat.com> 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
> 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). That did the trick, thanks! Just for reference: CREATE FUNCTION time_machine ( TIMESPAN ) RETURNS TIMESTAMP AS 'SELECT CURRENT_TIMESTAMP - $1' LANGUAGE 'sql' WITH (iscachable); seems to work. Perhaps it's more complicated than that, though? -itai