Forum OpenACS Q&A: Re: Best way to cache a multirow?

Posted by Peter Marklund on
For the record, I ran vacuumdb and observed this monstruous query on the index page of Bookmarks:

      select count(*) from bm_bookmarks
                    where owner_id = :viewed_user_id
                    and folder_p = 't'
                    and acs_permission__permission_p(bookmark_id, :browsing_user_id, 'write')

The query went from 6 to 4 seconds, not quite the improvement I had hoped for. Vacuumdb does make a significant difference though and I'll start doing it nightly. Admittedly, the server has an old AMD 400 MHZ something processor. I have plenty of ram though, according to top about 290 MB free.

Don, could you send me the db -cache code. I'd be very curious to see if I could use it.



Posted by Jeff Davis on
you should also check and make sure you increased your shared buffers on postgresql and that the db cache is populated. It might be that in practice it is much faster once it has run a couple times.

Whats the result of "explain analyze select ..." on that query?