Subject: Re: Protect syscache from bloating with negative cache
 entries
To: Tom Lane <tgl@sss.pgh.pa.us>
References: 
 <CA+TgmoaMPbN5vvH33zLK7MR+PiCYWPvTHmSSrDab4vurLOXS-Q@mail.gmail.com>
 <23492.1482264621@sss.pgh.pa.us>
 <20161221.102109.51106943.horiguchi.kyotaro@lab.ntt.co.jp>
 <20161226.173147.73783918.horiguchi.kyotaro@lab.ntt.co.jp>
 <ecd7ba88-0484-54be-5b8a-c48848438aea@BlueTreble.com>
 <19334.1485053684@sss.pgh.pa.us>
 <276bcca0-9bfd-e60f-243d-baf081b41946@BlueTreble.com>
CC: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
	<robertmhaas@gmail.com>, <craig@2ndquadrant.com>,
	<pgsql-hackers@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <683113d7-c7a7-1a0f-da7c-b197b08a6440@BlueTreble.com>
Date: Sun, 22 Jan 2017 16:51:53 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: <276bcca0-9bfd-e60f-243d-baf081b41946@BlueTreble.com>
Content-Type: multipart/mixed;
	boundary="------------592939E38622641A17C2DB2E"
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 Jan 2017 22:59:52.0213 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MWHPR11MB1646
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

--------------592939E38622641A17C2DB2E
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit

On 1/22/17 4:41 PM, Jim Nasby wrote:
> On 1/21/17 8:54 PM, Tom Lane wrote:
>> Jim Nasby <Jim.Nasby@bluetreble.com> writes:
>>> The other (possibly naive) question I have is how useful negative
>>> entries really are? Will Postgres regularly incur negative lookups, or
>>> will these only happen due to user activity?
>> It varies depending on the particular syscache, but in at least some
>> of them, negative cache entries are critical for performance.
>> See for example RelnameGetRelid(), which basically does a RELNAMENSP
>> cache lookup for each schema down the search path until it finds a
>> match.
>
> Ahh, I hadn't considered that. So one idea would be to only track
> negative entries on caches where we know they're actually useful. That
> might make the performance hit of some of the other ideas more
> tolerable. Presumably you're much less likely to pollute the namespace
> cache than some of the others.

Ok, after reading the code I see I only partly understood what you were 
saying. In any case, it might still be useful to do some testing with 
CATCACHE_STATS defined to see if there's caches that don't accumulate a 
lot of negative entries.

Attached is a patch that tries to document some of this.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

--------------592939E38622641A17C2DB2E
Content-Type: text/plain; charset="UTF-8"; x-mac-type=0; x-mac-creator=0;
	name="negative.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="negative.patch"

diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 253c7b53ed..d718d82d80 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -101,7 +101,12 @@ typedef struct catctup
 	 *
 	 * A negative cache entry is an assertion that there is no tuple matching
 	 * a particular key.  This is just as useful as a normal entry so far as
-	 * avoiding catalog searches is concerned.  Management of positive and
+	 * avoiding catalog searches is concerned.  In particular, caching negative
+	 * entries is critical for performance of some caches. For example, current
+	 * code will produce a negative RELNAMENSP entry for every un-qualified
+	 * table looking with the default search_path that puts the pg_catalog
+	 * schema first. This effect can be obverved by defining CATCACHE_STATS and
+	 * observing the log at backend exit.  Management of positive and
 	 * negative entries is identical.
 	 */
 	int			refcount;		/* number of active references */

--------------592939E38622641A17C2DB2E
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------592939E38622641A17C2DB2E--