pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndices and time spans
3+ messages / 2 participants
[nested] [flat]
* Indices and time spans
@ 2000-07-31 13:44 Itai Zukerman <zukerman@math-hat.com>
0 siblings, 1 reply; 3+ messages in thread
From: Itai Zukerman @ 2000-07-31 13:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indices and time spans
@ 2000-07-31 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Itai Zukerman <zukerman@math-hat.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2000-07-31 14:49 UTC (permalink / raw)
To: Itai Zukerman <zukerman@math-hat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indices and time spans
@ 2000-07-31 16:07 Itai Zukerman <zukerman@math-hat.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Itai Zukerman @ 2000-07-31 16:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-07-31 16:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-31 13:44 Indices and time spans Itai Zukerman <zukerman@math-hat.com>
2000-07-31 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-31 16:07 ` Itai Zukerman <zukerman@math-hat.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox