Re: [HACKERS] FSM versus GIN pending list bloat

On Thu, Sep 3, 2015 at 3:57 AM, Robert Haas wrote: > On Mon, Aug 10, 2015 at 1:16 PM, Jeff Janes wrote: >> I have a simple test case that inserts an array of 101 md5 digests into each >> row. With 10_000 of these rows inserted into an already indexed

Re: [HACKERS] FSM versus GIN pending list bloat

On Mon, Aug 10, 2015 at 1:16 PM, Jeff Janes wrote: > I have a simple test case that inserts an array of 101 md5 digests into each > row. With 10_000 of these rows inserted into an already indexed table, I > get 40MB for the table and 80MB for the index unpatched. With the

Re: [HACKERS] FSM versus GIN pending list bloat

On Wed, Aug 5, 2015 at 5:50 AM, Simon Riggs wrote: > On 4 August 2015 at 21:04, Jeff Janes wrote: > >>> >>> Couple of questions here... >>> >>> * the docs say "it's desirable to have pending-list cleanup occur in the >>> background", but there is no

Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 12:38 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Tue, Aug 4, 2015 at 1:39 AM, Simon Riggs si...@2ndquadrant.com wrote: On 4 August 2015 at 06:03, Jeff Janes jeff.ja...@gmail.com wrote: The attached proof of concept patch greatly improves the bloat for both the

Re: [HACKERS] FSM versus GIN pending list bloat

On 4 August 2015 at 06:03, Jeff Janes jeff.ja...@gmail.com wrote: The attached proof of concept patch greatly improves the bloat for both the insert and the update cases. You need to turn on both features: adding the pages to fsm, and vacuuming the fsm, to get the benefit (so JJ_GIN=3). The

Re: [HACKERS] FSM versus GIN pending list bloat

On 08/04/2015 04:35 PM, Simon Riggs wrote: This and the OP seem like 9.5 open items to me. Why? This is nothing new in 9.5. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] FSM versus GIN pending list bloat

On 4 August 2015 at 14:55, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/04/2015 04:35 PM, Simon Riggs wrote: This and the OP seem like 9.5 open items to me. Why? This is nothing new in 9.5. gin_pending_list_limit is new in 9.5 We're in Alpha, so if something has been added and isn't

Re: [HACKERS] FSM versus GIN pending list bloat

On 2015-08-04 14:59:11 +0100, Simon Riggs wrote: On 4 August 2015 at 14:55, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/04/2015 04:35 PM, Simon Riggs wrote: This and the OP seem like 9.5 open items to me. Why? This is nothing new in 9.5. gin_pending_list_limit is new in 9.5

Re: [HACKERS] FSM versus GIN pending list bloat

On 08/04/2015 08:03 AM, Jeff Janes wrote: For a GIN index with fastupdate turned on, both the user backends and autoanalyze routine will clear out the pending list, pushing the entries into the normal index structure and deleting the pages used by the pending list. But those deleted pages will

Re: [HACKERS] FSM versus GIN pending list bloat

On 4 August 2015 at 09:39, Simon Riggs si...@2ndquadrant.com wrote: On 4 August 2015 at 06:03, Jeff Janes jeff.ja...@gmail.com wrote: The attached proof of concept patch greatly improves the bloat for both the insert and the update cases. You need to turn on both features: adding the pages

Re: [HACKERS] FSM versus GIN pending list bloat

On 4 August 2015 at 15:18, Andres Freund and...@anarazel.de wrote: On 2015-08-04 14:59:11 +0100, Simon Riggs wrote: On 4 August 2015 at 14:55, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/04/2015 04:35 PM, Simon Riggs wrote: This and the OP seem like 9.5 open items to me.

Re: [HACKERS] FSM versus GIN pending list bloat

Simon Riggs si...@2ndquadrant.com writes: On 4 August 2015 at 15:18, Andres Freund and...@anarazel.de wrote: The only thing that variable does is change what the pending size limit is determined by. Previously it was work_mem, now it's gin_pending_list_limit. Imo that has pretty much nothing

Re: [HACKERS] FSM versus GIN pending list bloat

Jeff Janes wrote: The attached proof of concept patch greatly improves the bloat for both the insert and the update cases. You need to turn on both features: adding the pages to fsm, and vacuuming the fsm, to get the benefit (so JJ_GIN=3). The first of those two things could probably be

Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 1:39 AM, Simon Riggs si...@2ndquadrant.com wrote: On 4 August 2015 at 06:03, Jeff Janes jeff.ja...@gmail.com wrote: The attached proof of concept patch greatly improves the bloat for both the insert and the update cases. You need to turn on both features: adding the

Re: [HACKERS] FSM versus GIN pending list bloat

On Tue, Aug 4, 2015 at 6:35 AM, Simon Riggs si...@2ndquadrant.com wrote: On 4 August 2015 at 09:39, Simon Riggs si...@2ndquadrant.com wrote: On 4 August 2015 at 06:03, Jeff Janes jeff.ja...@gmail.com wrote: The attached proof of concept patch greatly improves the bloat for both the insert

Re: [HACKERS] FSM versus GIN pending list bloat

On 4 August 2015 at 21:04, Jeff Janes jeff.ja...@gmail.com wrote: Couple of questions here... * the docs say it's desirable to have pending-list cleanup occur in the background, but there is no way to invoke that, except via VACUUM. I think we need a separate function to be able to call

[HACKERS] FSM versus GIN pending list bloat

For a GIN index with fastupdate turned on, both the user backends and autoanalyze routine will clear out the pending list, pushing the entries into the normal index structure and deleting the pages used by the pending list. But those deleted pages will not get added to the freespace map until a