Re: Extreme bloating of intarray GiST indexes

> No, because you have under 10% dead tuples in the main table.
> I think this is sufficient proof of the crummy-page-splits theory.
> Can you provide the data in the column that's indexed?

Yes, I can.   Fortunately, none of it's identifiable.

Attached.  This is for the index which is 90% free space.

So, some other characteristics of this index:

* If you didn't notice earlier, it's a partial index.  The two columns
which determine the partial index change more often than the intarray
column.

* We've also determined some other unusual patterns from watching the
application:

(a) the "listings" table is a very wide table, with about 60 columns

(b) whenever the table gets updated, the application code updates these
60 columns in 4 sections.  So there's 4 updates to the same row, in a
single transaction.

(c) we *think* that other columns of the table, including other indexed
columns, are changed much more frequently than the intarray column is.
Currently doing analysis on that.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com