Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bwaGL-0004BA-Lo for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Oct 2016 19:41:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bwaGL-0005z3-5o for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Oct 2016 19:41:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bwaGK-0005yw-5o for pgsql-hackers@postgresql.org; Tue, 18 Oct 2016 19:41:24 +0000 Received: from lists.commandprompt.com ([2604:da00:2:2:5054:ff:fe07:7618]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bwaGF-00066B-T7 for pgsql-hackers@postgresql.org; Tue, 18 Oct 2016 19:41:22 +0000 Received: from [192.168.1.61] (spinlock.commandprompt.com [69.168.55.30]) by lists.commandprompt.com (Postfix) with ESMTPA id 5C4F51A; Tue, 18 Oct 2016 12:41:17 -0700 (PDT) Received: from c-73-225-27-179.hsd1.wa.comcast.net ([73.225.27.179] helo=[192.168.1.61]) by assp.commandprompt.com with SMTPSA(TLSv1_2 ECDHE-RSA-AES128-GCM-SHA256) (2.5.1); 18 Oct 2016 12:41:16 -0700 Subject: Re: Indirect indexes To: Alvaro Herrera , Pg Hackers References: <20161018182843.xczrxsa2yd47pnru@alvherre.pgsql> From: "Joshua D. Drake" Organization: Command Prompt, Inc. Message-ID: <2f5e6c38-4103-3def-2d6a-24d6509ed34e@commandprompt.com> Date: Tue, 18 Oct 2016 12:41:16 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.3.0 MIME-Version: 1.0 In-Reply-To: <20161018182843.xczrxsa2yd47pnru@alvherre.pgsql> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Assp-Version: 2.5.1(16177) on assp.commandprompt.com X-Assp-ID: assp.commandprompt.com m1-19677-05369 X-Assp-Session: 7FA8046EB548 (mail 1) X-Assp-Original-Subject: Re: [HACKERS] Indirect indexes X-Assp-Client-SSL: yes X-Pg-Spam-Score: -2.2 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 10/18/2016 11:28 AM, Alvaro Herrera wrote: > Vacuuming presents an additional challenge: in order to remove index > items from an indirect index, it's critical to scan the PK index first > and collect the PK values that are being removed. Then scan the > indirect index and remove any items that match the PK items removed. > This is a bit problematic because of the additional memory needed to > store the array of PK values. I haven't implemented this yet. As a whole I think the idea is interesting but the above scares me. Are we trading initial performance gains for performance degradation through maintenance? Since autovacuum is an indeterminate launch we could have a situation where even a medium level updated laden table becomes a source of contentions for IO and memory resources. I don't know that we would see issues on modern bare metal but considering our implementation space is places like RDS and GCE now, this is a serious consideration. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. Everyone appreciates your honesty, until you are honest with them. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers