From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Teodor Sigaev <teodor@sigaev.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCHES] GIN improvements
Date: Thu, 27 Nov 2008 22:36:41 +0200
Message-ID: <492F04D9.5070404@enterprisedb.com> (raw)
In-Reply-To: <490B07BA.6030109@sigaev.ru>
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>
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
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-patches@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, teodor@sigaev.ru, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] GIN improvements
In-Reply-To: <492F04D9.5070404@enterprisedb.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