Re: [PATCHES] GIN improvements

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