Need help with query involving automatically decaying values

[xadrian]

Hi,

I'm trying to put together a query that will order entries in a table by the rating values which decay over time. I'm not too well versed in the intricacies of Postgres, or complex queries, but I've cobbled the following together:

select * from entries order by (
   select sum(-rating_value * exp(ln(0.5)/43200 * 
                    extract(epoch from now() - rating_time))) 
   from ratings where ratings.entry_id = entries.id)
 

There are two tables - entries and ratings. The ratings table stores a few values such as the time of the rating, the user who contributed the rating, the initial value, the related entry id, etc. The -rating_value was just thrown in while I was testing and could just as easily be replaced by a DESC. The ln(0.5)/43200 causes the rating to lose half its current value every 12 hours.

It seems that as the tables grow, this query could start taking quite a bit of time (it's already quite a bit slower than my average query), so I had a few questions:

1.) Is there a much better way to do this, via JOINs or something else? I was unsuccessful in constructing a working JOIN.

2.) Is there any way to reduce the number of selects? This will run a select for each entry in the database to grab a rating value for ordering.

3.) Is there a better formula that would prevent the need for a SUM on every select everytime the query is run? It is important to retain the shown behavior so that an entry that receives 500 good ratings today will show up as "popular" for the moment, but if nobody ever votes on it again we don't have to wait until the 500 vote record is broken to see the more recent popular items. Basically, the items will slowly settle into their "overall" popular positions after enjoying the spotlight for a few days.

I appreciate any help. Thanks in advance!

-Dave