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

In response to

• Re: [PATCHES] GIN improvements at 2008-10-31 13:27:22 from Teodor Sigaev

Responses

• Re: [PATCHES] GIN improvements at 2008-11-27 22:14:59 from Gregory Stark
• Re: [PATCHES] GIN improvements at 2008-12-02 12:56:33 from Teodor Sigaev
• Re: [PATCHES] GIN improvements at 2008-12-12 17:36:55 from Teodor Sigaev

pgsql-hackers by date

Next:	From: Heikki Linnakangas	Date: 2008-11-27 20:37:48
	Subject: Re: Visibility map, partial vacuums
Previous:	From: Simon Riggs	Date: 2008-11-27 19:45:34
	Subject: Re: Distinct types

pgsql-patches by date

Next:	From: Gregory Stark	Date: 2008-11-27 22:14:59
	Subject: Re: [PATCHES] GIN improvements
Previous:	From: Robert Haas	Date: 2008-11-27 16:38:39
	Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1