From: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: Function search_path
Date: Thu, 29 Mar 2018 19:57:13 +0000
Message-ID: <917d713d16f548a3870adde2a7982a39@THALASSA.omni.imsweb.com> (raw)
In-Reply-To: <11207.1521998011@sss.pgh.pa.us>
References: <db194d98f61d4aa398c1399a24326e7c@THALASSA.omni.imsweb.com>
<5f698c34-d01d-3196-1db4-75ba8f6a37d3@portavita.eu>
<b0078bdae3e04de1ba3ddaa3527c8922@THALASSA.omni.imsweb.com>
<2328.1521236730@sss.pgh.pa.us>
<da2ee034500f4639b7c03b58f18f147a@THALASSA.omni.imsweb.com>
<11207.1521998011@sss.pgh.pa.us>
>> Here is an example where I can show significant extra memory consumption when setting search_path on a function:
>
>I got around to testing this example today, and I don't see what you're seeing --- the memory consumption seems stable, and about the same with or without the "SET search_path" clause.
>
>You didn't specify exactly how to do this bit:
>
>--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
>
>so I did it like this:
>
>INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);
>
>The example doesn't seem like it'd be terribly data-dependent, but maybe that's wrong?
>
>Also, I assume you're running a reasonably up-to-date PG release, otherwise I'd be wondering about leaks in the GIN index AM; we've fixed some issues of that sort in GIN bulk updates in the past.
>But again, it's not clear what the connection to a function SET clause would be. So I lack a plausible theory at the moment.
Tom,
Thanks for your looking at this.
I tested this well on 9.6.7 but I saw the same issue on 10.3.
You are correct the issue is not data dependant and I saw the same issues with the random numeric inserts you used.
On a server with a lot of memory(100GB) and using the postgres default memory settings, the update was able to complete but when watching top there was a clear spike in %MEM for the update when the function search_path was set.
The issue becomes more noticeable as you add more rows, for example at 5,000,000 %MEM went over 35. Without the search_path set %MEM stays real low and the update was a lot quicker.
Here is the more complete test case:
CREATE SCHEMA test_search_path AUTHORIZATION test_search_path;
--then logged in as the test_search_path user
CREATE TABLE test_search_path(date_last_modified timestamp, last_name varchar);
INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);
CREATE OR REPLACE FUNCTION clean_name_upper(original_name varchar)
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(upper(original_name));
END;
$$ IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION split_name_on_hyphen(original_name character varying)
RETURNS text[]
LANGUAGE plpgsql
AS $$
BEGIN
RETURN string_to_array(clean_name_upper(original_name), '-');
END;
$$ IMMUTABLE STRICT SET search_path = '$user';
CREATE INDEX idx_test_search_path_clean_name_upper_last_name ON test_search_path (clean_name_upper(last_name));
CREATE INDEX idx_test_search_path_split_name_on_hyphen_last_name ON test_search_path USING gin(split_name_on_hyphen(last_name));
UPDATE test_search_path SET date_last_modified = (date_last_modified - interval '7 days');
There seems to be something about the inner function (clean_name_upper) being used in an index of its own on the same field as the GIN functional index (split_name_on_hyphen) that is causing this.
Thanks,
Manfred
________________________________
Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
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-admin@postgresql.org
Cc: HeinemannM@imsweb.com, tgl@sss.pgh.pa.us, pgsql-admin@lists.postgresql.org
Subject: RE: Function search_path
In-Reply-To: <917d713d16f548a3870adde2a7982a39@THALASSA.omni.imsweb.com>
* 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