> 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