Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aRi0a-00073N-11 for pgsql-performance@arkaria.postgresql.org; Fri, 05 Feb 2016 15:09:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aRi0Y-0005L6-MD for pgsql-performance@arkaria.postgresql.org; Fri, 05 Feb 2016 15:09:14 +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) (envelope-from ) id 1aRhyu-0003RL-AG for pgsql-performance@postgresql.org; Fri, 05 Feb 2016 15:07:32 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aRhym-0008Cg-QS for pgsql-performance@postgresql.org; Fri, 05 Feb 2016 15:07:30 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id u15F7I9L012467; Fri, 5 Feb 2016 10:07:18 -0500 From: Tom Lane To: Marc Mamin cc: "'pgsql-performance@postgresql.org'" Subject: Re: gin performance issue. In-reply-to: References: Comments: In-reply-to Marc Mamin message dated "Fri, 05 Feb 2016 11:28:08 +0000" Date: Fri, 05 Feb 2016 10:07:17 -0500 Message-ID: <12466.1454684837@sss.pgh.pa.us> X-Pg-Spam-Score: -2.2 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Marc Mamin writes: > http://explain.depesz.com/s/wKv7 > Postgres Version 9.3.10 (Linux) > Hello, > this is a large daily table that only get bulk inserts (200-400 /days) with no update. > After rebuilding the whole table, the Bitmap Index Scan on r_20160204_ix_toprid falls under 1 second (from 800) > Fastupdate is using the default, but autovacuum is disabled on that table which contains 30 Mio rows. Pre-9.5, it's a pretty bad idea to disable autovacuum on a GIN index, because then the "pending list" only gets flushed when it exceeds work_mem. (Obviously, using a large work_mem setting makes this worse.) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance