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
Harmon,
> A "VACUUM FULL ANALYZE" is performed every 3 hours.
The FULL part should not be necessary if you've set your max_fsm_pages high
enough.
> Given there are 10080 minutes per week, the planner could, properly
> configured, estimate the number of rows returned by such a query to be:
>
>
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
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
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
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 t
"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 p
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 t
We were getting a little desperate, so we engaged in overkill to rule
out lack-of-analyze as a cause for the slow queries.
Thanks for your advice :)
-- Harmon
Matthew T. O'Connor wrote:
VACUUM FULL ANALYZE every 3 hours seems a little severe. You will
probably be be served just as well by VACUU
Thank you for your response :)
This improves the row estimation, but it is still using a sequential scan.
It really seems like the query would go faster if an index scan was
used, given the number of rows fetched (both estimated and actual) is
significantly less than the number of rows in the tab
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 mu
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 minut
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 table
13 matches
Mail list logo
