rsmogura <rsmogura(at)softperience(dot)eu> wrote:
> Yes, there is some change, and I looked at this more carefully, as
> my performance results wasn't such as I expected. I found PG uses
> BufferAccessStrategy to do sequence scans, so my test query took
> only 32 buffers from pool and didn't overwritten index pool too
> much. This BAS is really surprising. In any case when I end
> polishing I will send good patch, with proof.
Yeah, that heuristic makes this less critical, for sure.
> Actually idea of this patch was like this:
> Some operations requires many buffers, PG uses "clock sweep" to
> get next free buffer, so it may overwrite index buffer. From point
> of view of good database design We should use indices, so purging
> out index from cache will affect performance.
>
> As the side effect I saw that this 2nd level keeps pg_* indices
> in memory too, so I think to include 3rd level cache for some pg_*
> tables.
Well, the more complex you make it the more overhead there is, which
makes it harder to come out ahead. FWIW, in musing about it (as
recently as this week), my idea was to add another field which would
factor into the clock sweep calculations. For indexes, it might be
"levels above leaf pages". I haven't reviewed the code in depth to
know how to use it, this was just idle daydreaming based on that
prior experience. It's far from certain that the concept will
actually prove beneficial in PostgreSQL.
Maybe the thing to focus on first is the oft-discussed "benchmark
farm" (similar to the "build farm"), with a good mix of loads, so
that the impact of changes can be better tracked for multiple
workloads on a variety of platforms and configurations. Without
something like that it is very hard to justify the added complexity
of an idea like this in terms of the performance benefit gained.
-Kevin