Re: question about index cost estimates

Jeff Hoffmann <jeff@propertykey.com> writes:
> the question is this: shouldn't you be able to make an educated guess at
> this by dividing the total buffer space allocated by the backend by the
> number of postmaster processes running at the time?  or don't you know
> those things?

Two things here:

One, we could easily find out the number of active backends, and we
certainly know the number of shared disk buffers.  BUT: it'd be a
debugging nightmare if the planner's choices depended on the number
of other backends that were running at the instant of planning.  Even
though that'd theoretically be the right thing to do, I don't think
we want to go there.  (If you want an argument less dependent on
mere implementation convenience, consider that in many scenarios
the N backends will be accessing more or less the same set of tables.
So the assumption that each backend only gets the use of 1/N of the
shared buffer space is too pessimistic anyway.)

Two, the Postgres shared buffer cache is only the first-line cache.
We also have the Unix kernel's buffer cache underneath us, though
we must share it with whatever else is going on on the machine.
As far as I've been able to measure there is relatively little cost
difference between finding a page in the Postgres cache and finding
it in the kernel cache --- certainly a kernel call is still much
cheaper than an actual disk access.  So the most relevant number
seems to be the fraction of the kernel's buffer cache that's
effectively available to Postgres.  Right now we have no way at
all to measure that number, so we punt and treat it as a user-
settable parameter (I think I made the default setting 10Mb or so).
It'd be worthwhile looking into whether we can do better than
guessing about the kernel cache size.
        regards, tom lane