Re: Extreme bloating of intarray GiST indexes

All,

Some trending data, since there's a lot of bloated indexes here:

select 'index_' || ( row_number() over ( order by free_percent desc ) )
as "index", *
from (
select (public.pgstattuple(indexname::text)).free_percent,
round(( n_tup_upd )::numeric / n_tup_ins, 2) as update_ratio,
round(( n_tup_hot_upd )::numeric / n_tup_ins, 2) as hot_update_ratio,
round(( n_tup_del * 100 )::numeric / n_tup_ins)  as delete_percent,
extract('days' from ( now() - last_autovacuum )) as days_since_vac,
n_live_tup / 1000 as "1K_tuples"
from pg_indexes join pg_stat_user_tables as tables
ON pg_indexes.schemaname = tables.schemaname
AND pg_indexes.tablename = tables.relname
where indexname like '%__listings_features'
) as idxstats

order by free_percent desc;
index|free_percent|update_ratio|hot_update_ratio|delete_percent|days_since_vac|1K_tuples
index_1|90.97|34.30|13.68|50|3|9
index_2|87.14|15.54|2.99|41|1|2
index_3|85.08|10.86|1.42|35|5|77
index_4|84.28|22.27|5.47|18|4|370
index_5|82.4|13.65|3.89|24|49|82
index_6|82.2|11.32|2.22|29|3|54
index_7|80.97|14.38|2.95|6|14|17
index_8|80.59|15.64|2.73|48|1|29
index_9|78.43|12.81|2.97|21|37|42
index_10|77.91|11.24|2.33|57|1|21
index_11|77.26|12.73|2.00|18|11|55
index_12|77.07|16.62|2.71|15|7|7
index_13|76.56|12.20|3.20|11|11|18
index_14|75.94|14.52|2.00|23|13|15
index_15|74.73|14.94|2.68|17|11|34
index_16|73.78|15.94|3.77|25|5|2
index_17|73.54|50.19|4.26|14|14|10
index_18|73.11|15.07|6.70|20|20|7
index_19|72.82|10.26|4.63|19|11|7
index_20|72.55|15.59|5.14|22|3|13
index_21|68.52|19.69|5.49|13|11|3
index_22|61.47|14.00|4.61|27|47|2
index_23|45.06|18.10|11.65|19|96|2
index_24|37.75|6.04|1.32|36|96|15
index_25|36.87|15.32|3.71|10|96|17
index_26|32.32|7.07|2.15|18|96|15
index_27|0|6.28|0.74|10|316|48

This makes a pretty graph, but the only thing it tells me is that the
handful of non-bloated tables are the ones which weren't vacuumed
recently, and either have very few rows or haven't gotten a lot of
updates.  This is not a surprise.

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