Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 604BF64FDF5 for ; Thu, 27 Nov 2008 16:36:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 60321-04 for ; Thu, 27 Nov 2008 16:36:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.24]) by mail.postgresql.org (Postfix) with ESMTP id A57B26501F1 for ; Thu, 27 Nov 2008 16:36:47 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 6so495862eyi.61 for ; Thu, 27 Nov 2008 12:36:46 -0800 (PST) Received: by 10.210.40.10 with SMTP id n10mr7968036ebn.13.1227818205545; Thu, 27 Nov 2008 12:36:45 -0800 (PST) Received: from ?80.222.79.63? (dsl-hkibrasgw2-fe4fde00-63.dhcp.inet.fi [80.222.79.63]) by mx.google.com with ESMTPS id d24sm2084892nfh.16.2008.11.27.12.36.43 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 27 Nov 2008 12:36:44 -0800 (PST) Message-ID: <492F04D9.5070404@enterprisedb.com> Date: Thu, 27 Nov 2008 22:36:41 +0200 Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.17 (X11/20081018) MIME-Version: 1.0 To: Teodor Sigaev CC: Tom Lane , Pgsql Hackers Subject: Re: [PATCHES] GIN improvements References: <483FD205.4090904@sigaev.ru> <486BC501.6060605@sigaev.ru> <27880.1215810535@sss.pgh.pa.us> <487A6E0C.3090600@sigaev.ru> <8993.1216843701@sss.pgh.pa.us> <4888A594.6010102@sigaev.ru> <490B07BA.6030109@sigaev.ru> In-Reply-To: <490B07BA.6030109@sigaev.ru> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit From: Heikki Linnakangas X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.315 tagged_above=0 required=5 tests=SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 200811/1833 X-Sequence-Number: 128545 There's a pretty fundamental issue with this patch, which is that while buffering the inserts in the "list pages" makes the inserts fast, all subsequent queries become slower until the tuples have been properly inserted into the index. I'm sure it's a good tradeoff in many cases, but there has got to be a limit to it. Currently, if you create an empty table, and load millions of tuples into it using INSERTs, the index degenerates into just a pile of "fast" tuples that every query needs to grovel through. The situation will only be rectified at the next vacuum, but if there's no deletes or updates on the table, just inserts, autovacuum won't happen until the next anti-wraparound vacuum. To make things worse, a query will fail if all the matching fast-inserted tuples don't fit in the non-lossy tid bitmap. That's another reason to limit the number of list pages; queries will start failing otherwise. Yet another problem is that if so much work is offloaded to autovacuum, it can tie up autovacuum workers for a very long time. And the work can happen on an unfortunate time, when the system is busy, and affect other queries. There's no vacuum_delay_point()s in gininsertcleanup, so there's no way to throttle that work. I think we need a hard limit on the number of list pages, before we can consider accepting this patch. After the limit is full, the next inserter can flush the list, inserting the tuples in the list into the tree, or just fall back to regular, slow, inserts. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com