From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Andres Freund <andres@anarazel.de>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: David Steele <david@pgmasters.net>
Cc: Jim Nasby <Jim.Nasby@bluetreble.com>
Cc: Craig Ringer <craig@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Protect syscache from bloating with negative cache entries
Date: Tue, 19 Dec 2017 13:14:09 -0500
Message-ID: <748.1513707249@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+TgmoYbVQo3iPByWQ535Bo-SXqCc3091o0gaVD8GJiXq7r2Fg@mail.gmail.com>
References: <CA+TgmoZE7jq2BMOmOqiwGYaDb1ZEvXyFpbKP3rJwzax6VQbk7g@mail.gmail.com>
<20171218164653.qg7sm6xtn7zfx2hi@alap3.anarazel.de>
<CA+TgmoaWLBzUasvVs-q=dfBr3pLWSUCQnbqLk-MT7iX4eyrinA@mail.gmail.com>
<20171219.173138.46350691.horiguchi.kyotaro@lab.ntt.co.jp>
<CA+TgmoYbVQo3iPByWQ535Bo-SXqCc3091o0gaVD8GJiXq7r2Fg@mail.gmail.com>
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Dec 19, 2017 at 3:31 AM, Kyotaro HORIGUCHI
> <horiguchi.kyotaro@lab.ntt.co.jp> wrote:
>> I see three kinds of syscache entries.
>>
>> A. An entry for an actually existing object.
>> B. An entry for an object which once existed but no longer.
>> C. An entry for a just non-existent objects.
> I'm not convinced that it's useful to divide things up this way.
Actually, I don't believe that case B exists at all; such an entry
should get blown away by syscache invalidation when we commit the
DROP command. If one were to stick around, we'd risk false positive
lookups later.
> I guess I still don't see why B is different. If somebody sits there
> and runs queries against non-existent table names at top speed, maybe
> they'll query the same non-existent table entries more than once, in
> which case keeping the negative entries for the non-existent table
> names around until they stop doing it may improve performance.
FWIW, my recollection is that the reason for negative cache entries
is that there are some very common patterns where we probe for object
names (not just table names, either) that aren't there, typically as
a side effect of walking through the search_path looking for a match
to an unqualified object name. Those cache entries aren't going to
get any less useful than the positive entry for the ultimately-found
object. So from a lifespan point of view I'm not very sure that it's
worth distinguishing cases A and C.
It's conceivable that we could rewrite all the lookup algorithms
so that they didn't require negative cache entries to have good
performance ... but I doubt that that's easy to do.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, horiguchi.kyotaro@lab.ntt.co.jp, andres@anarazel.de, michael.paquier@gmail.com, david@pgmasters.net, Jim.Nasby@bluetreble.com, craig@2ndquadrant.com
Subject: Re: Protect syscache from bloating with negative cache entries
In-Reply-To: <748.1513707249@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox