07 November 07 - 18:18in response to: Need Help Reducing View Calculations

I'm posting this via trackback because of a strange posting problem:  (layout is strange and missing the email field?  Yes I am using a crud browser from here... that could be it) 

I have to agree with Hernan.

The simplest saving I can see is an extra column to save on the "run time" calculation.  This does not have to be dangerous (ie potentially different to the date column you have already)Just setup a trigger for whenever the existing date column gets updated(/inserted) to update the extra column.

Another alternative is to use a materialised view.  On Postgres this is a manual process involving setting up a view and rules, and ig you want it always up to date, triggers again on the source table to update the materialised view whenever the data changes. 

This latter idea would be the fastest - faster than your original view by quite a margin, in fact.  You may then be able to get rid of some (all?) indexes you have already on the existing table - because they would no longer be needed.

One thing I think would be handy that I have on my list of "when I get time" is to write some reasonably automatic code to do materialised views without the manual coding (ala Oracle).  People may use these things more if they were a bit more simple to do.

Link to Need Help Reducing View Calculations page: http://www.justatheory.com/computers/databases/postgresql/reducing_view_calculations.html

- postgresql - No comments / No trackback - § ¶