Re: Extreme bloating of intarray GiST indexes

Tom, Alexander,

So, some data:

corp=# select indexname,
pg_size_pretty(pg_relation_size(indexname::text)) as indexsize,
pg_size_pretty(pg_relation_size(tablename::text)) as tablesize
from pg_indexes where indexname like '%__listings_features' order by
pg_relation_size(indexname::text) desc;
indexname | indexsize | tablesize
---------------------------------------+------------+------------
idx__listings_features | 52 MB | 20 MB

corp=# select * from pg_indexes where indexname = 'idx__listings_features';
-[ RECORD 1
]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | boards
tablename | listings
indexname | idx__listings_features
tablespace |
indexdef | CREATE INDEX idx__listings_features ON listings USING gist
(features public.gist__intbig_ops) WHERE ((deleted_at IS NULL) AND
(status_id = 1))

corp=# select * from public.pgstattuple('idx__listings_features');
-[ RECORD 1 ]------+---------
table_len | 54190080
tuple_count | 7786
tuple_len | 2117792
tuple_percent | 3.91
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 49297536
free_percent | 90.97
^^^^^^^^^
Well, that explains the bloating. Why all that free space, though?

Maybe autovac isn't running?

Nope:

corp=# select * from pg_stat_user_tables where relname = 'listings';

-[ RECORD 1 ]----+------------------------------
relid | 110919
schemaname | boards
relname | listings
seq_scan | 37492
seq_tup_read | 328794009
idx_scan | 33982523
idx_tup_fetch | 302782765
n_tup_ins | 19490
n_tup_upd | 668445
n_tup_del | 9826
n_tup_hot_upd | 266661
n_live_tup | 9664
n_dead_tup | 776
last_vacuum | 2010-07-25 19:46:45.922861+00
last_autovacuum | 2011-04-30 17:30:40.555311+00
last_analyze | 2010-07-25 19:46:45.922861+00
last_autoanalyze | 2011-04-28 23:49:54.968689+00

I don't know when stats were last reset (see, this is why we need a
reset timestamp!) so not sure how long those have been accumulating.

(note: object names changed for confidentiality)

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