On 2014-08-26 22:19:47 -0400, Tom Lane wrote: > Andres Freund <and...@2ndquadrant.com> writes: > > The biggest concern previously were some benchmarks. I'm not entirely > > sure where to get a good testcase for this that's not completely > > artificial - most simpler testcases don't pin many buffers. > > FWIW, I think that's by design; we don't ever want to pin more than one > buffer per relation+index in use in a given query.
Right. > You could certainly > build complicated queries joining many tables in order to push up the > number of pinned buffers, but whether buffer pin manipulations would be > the bottleneck in such cases is pretty dubious. Yea, I actually tried that and I didn't see anything. > I would say that the issue most deserving of performance testing is your > sizing of the linear-search array --- it's not obvious that 8 is a good > size. It's about the size of a cacheline on all common architectures, that's how I found it. I don't think it makes a very big difference whether we make it 4 or 12, but outside of that range I think it'll be unlikely to be beneficial. The regression tests never go about three or four pins or so currently, so I think that's a number unlikely to regularly be crossed in practice. > Another thing to think about: a way to get to larger numbers of pinned > buffers without especially-complex queries is to have nested queries, > such as SQL queries inside plpgsql functions inside outer queries. What I did was hack together a pgbench script that does a lot of DECLARE c_01 CURSOR FOR SELECT * FROM pg_attribute WHERE ctid = '(0, 1)'; FETCH NEXT FROM c_01; I couldn't measure a bigger slowdown (as that has to be executed for every xact) for the new code than for the old one. > Does the patch logic take any advantage of the locality-of-reference > that will occur in such scenarios? Yes. Whenever a buffer is pinned/unpinned that's not in the array it'll displace an entry from the array into the hashtable. Even though the replacement is simplistic/linear I think that should nearly always end up with the last used buffers in the array. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
