Strangely enough, I don't find that result surprising.

if the vast bulk of the data is in the past and now()-60 represents a very small slice 
of the data
we might expect that using an index is optimal, but there could be many reasons why it 
doesn't get

AFAIK postgres doesn't peek at values used in a query when optimizing so any query 
with a ">" type
condition is gonna have a seq scan as the plan since the best guess is that you are 
gonna match
50% of the table. That's one possible explanation.

Another is that if the condition data types don't match then an indes won't be used 
you could try:

  select count(*) from billing where timestamp > (now()-60)::timestamp

Might make a difference, I dunno, it's a case of testing amd seing what happens.

You could try lowering the random page cost, it might help, but I don't like your 

If your problem is that you want to access the most recent data from a large table 
with fast
response, then you could consider:

1. a "recent" index. If the data is within the "recent" time from set a flag to true, 
other wise
null. Reset the flags periodically. Nulls aren't indexed so the selectivity of such an 
index is
much higher. Can work wonders.

2, duplicate recent data in another table that is purged when data passes the age 
limit. This is
basic archiving.

Something like that. Hopefully someone with more knowlege of the optimaizer will have 
a brighter
suggestion for you. 

What version are you using by the way?
Mr Pink
--- Jack Kerkhof <[EMAIL PROTECTED]> wrote:

> The query:
>     select count(*) from billing where timestamp > now()-60
> should obviously use the index
>     CREATE INDEX billing_timestamp_idx ON billing USING btree ("timestamp"
> timestamp_ops);
> on a table with 1400000 rows.
> But it uses a Seq Scan. If I set enable_seqscan=no, it indicates a queryplan
> could not be calculated.
> Why does this simple query not use the timestamp index, and how can I get it
> to?
> Thanks, Jack
>      Jack Kerkhof
>       Research & Development
>       1.866.509.1010 3480
> --------------------------------------------------------------------------
>       Guest-Tek is a leading provider of broadband technology solutions for
> the hospitality industry. Guest-Tek's GlobalSuiteEhigh-speed Internet
> solution enables hotels to offer their guests the convenience of wired
> and/or wireless broadband Internet access from guest rooms, meeting rooms
> and public areas.

Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone. 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to