Re: Timestamp-based indexing

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:
>
> 10 min/ 10080 min * 400,000 = 0.001 * 400,000 = 400.

The planner doesn't work that way.

> 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(). What
happens when you supply a constant instead of ( current_timestamp - interval
'10 minutes' ) ?

> Here's something strange. We try to disable sequential scans, but to no
> avail. The estimated cost skyrockets, though:

That's how "enable_*=false" works in most cases.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco