Re: SQL select query becomes slow when using limit (with no offset)

Robert Haas wrote:
> On Mon, Aug 10, 2009 at 11:19 AM, Kevin Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> (2) Somehow use effective_cache_size in combination with some sort of
>> current activity metrics to dynamically adjust random access costs.
>> (I know, that one's total hand-waving, but it seems to have some
>> possibility of better modeling reality than what we currently do.)

I was disappointed when I learned that effective_cache_size doesn't get
generally used to predict the likelihood of a buffer fetch requiring
physical io.

> Yeah, I gave a lightning talk on this at PGcon, but I haven't had time
> to do anything with it. There are a couple of problems. One is that
> you have to have a source for your current activity metrics. Since a
> lot of the pages of interest will be in the OS buffer pool rather than
> PG shared buffers, there's no easy way to handle this

While there are portability concerns, mmap + mincore works across BSD,
Linux, Solaris and will return a vector of file pages in the OS buffer
pool. So it's certainly possible that on supported systems, an activity
monitor can have direct knowledge of OS caching effectiveness on a per
relation/index basis.

--
-Devin