From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Alexander Korotkov <aekorotkov(at)gmail(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Extreme bloating of intarray GiST indexes |
Date: | 2011-05-04 17:12:32 |
Message-ID: | 4DC18900.1050108@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
Attachment | Content-Type | Size |
---|---|---|
bloated_intarray.csv.zip | application/x-zip-compressed | 220.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2011-05-04 17:21:36 | Re: branching for 9.2devel |
Previous Message | Josh Berkus | 2011-05-04 17:04:51 | Re: patch for new feature: Buffer Cache Hibernation |