Forum OpenACS Development: Re: Compatibility Matrix

15: Re: Compatibility Matrix (response to 8)

Posted by Don Baccus on 02/02/12 10:55 PM

Try reducing random_page_cost to something lower than the default value 4, and raising the value of effective_cache_size to the size of your shared buffers + something reasonably large for the OS filesystem cache (and remember, modern disk drives cache too!).

We were having problems with unwanted sequential scans where indexed scans were obviously 50x faster in a production system I'm working on (NOT OpenACS/dotLRN) and by fiddling with these parameters I was able to persuade the planner to use indexed scans where it seemed to me it should.

I set random_page_cost to 1.0 ...

16: Re: Compatibility Matrix (response to 15)

Posted by Don Baccus on 02/02/12 10:58 PM

This was PG 9.1 BTW.

Let me also add that I tried recasting one of the offending queries several ways (in (SELECT ... IN (SELECT..., joins, join on subquery in FROM clause, etc) and got the same plan each time (except the nested IN SELECT forms lead to semi joins rather than full joins). That's good in that the planner's able to build a canonical form from each of my twisted efforts to fool it that's equivalent to the deeper meaning of what I was trying to do. It's bad in that each plan had a sequential scan of (gulp) 69 million rows!!!!