Josh Berkus [EMAIL PROTECTED] writes:
monitor=# explain analyze select * from eventtable where timestamp
CURRENT_TIMESTAMP - INTERVAL '10 minutes';
Hmmm. What verison of PostgreSQL are you running? I seem to remember an
issue in one version with selecting comparisons against now().
I'm
Hello --
To increase query (i.e. select) performance, we're trying to get
postgres to use an index based on a timestamp column in a given table.
Event-based data is put into this table several times a minute, with the
timestamp indicating when a particular row was placed in the table.
The
Harmon S. Nine wrote:
monitor=# explain analyze select * from eventtable where timestamp
CURRENT_TIMESTAMP - INTERVAL '10 minutes';
QUERY PLAN
Try
SELECT * FROM eventtable where timestamp BETWEEN (CURRENT_TIMESTAMP -
INTERVAL '10
VACUUM FULL ANALYZE every 3 hours seems a little severe. You will
probably be be served just as well by VACUUM ANALYZE. But you probably
don't need the VACUUM part most of the time. You might try doing an
ANALYZE on the specific tables you are having issues with. Since
ANALYZE should be
On Mon, 26 Jul 2004, Harmon S. Nine wrote:
However, we can't get the planner to do an timestamp-based index scan.
Anyone know what to do?
I'd wonder if the type conversion is causing you problems.
CURRENT_TIMESTAMP - INTERVAL '10 minutes' is a timestamp with time zone
while the column is
Matthew T. O'Connor [EMAIL PROTECTED] writes:
VACUUM FULL ANALYZE every 3 hours seems a little severe.
If rows are only deleted once a day, that's a complete waste of time,
indeed.
I'd suggest running a plain VACUUM just after the deletion pass is done.
ANALYZEs are a different matter and
THAT WAS IT!!
Thank you very much.
Is there a way to change the type of "CURRENT_TIMESTAMP" to "timestamp
without time zone" so that casting isn't needed?
BTW, isn't this a bug?
-- Harmon
Stephan Szabo wrote:
On Mon, 26 Jul 2004, Harmon S. Nine wrote:
However, we can't get
Hi,
How about changing:
CURRENT_TIMESTAMP - INTERVAL '10 minutes'
to
'now'::timestamptz - INTERVAL '10 minutes'
It seems to me that Postgres will treat it as
a constant.
Thanks,
--- Tom Lane [EMAIL PROTECTED] wrote:
Matthew T. O'Connor [EMAIL PROTECTED] writes:
VACUUM FULL ANALYZE every 3
Litao Wu [EMAIL PROTECTED] writes:
How about changing:
CURRENT_TIMESTAMP - INTERVAL '10 minutes'
to
'now'::timestamptz - INTERVAL '10 minutes'
It seems to me that Postgres will treat it as
a constant.
Yeah, that works too, though again it might burn you if used inside a
function or
It seems to me that Postgres will treat it as
a constant.
Yeah, that works too, though again it might burn you if used inside a
function or prepared statement. What you're doing here is to push the
freezing of the now value even further upstream, namely to initial
parsing of the command.
What I
10 matches
Mail list logo