Re: table clustering brings joy

Kevin Murphy <murphy(at)genome(dot)chop(dot)edu> writes:

> This is just an FYI for other people out there with large tables: table
> clustering sped up my queries from 10-100 times, which I am very happy about.
> I'm posting this in case it's ever useful to anybody. If someone reading this
> feels that I did something wrong, let me know.

One thing you should realize is that cluster effectively removes all dead
tuples from the table. If you had lots of dead tuples that could have been
slowing things down.

Vacuum only marks dead tuples for reuse. If you're not running vacuum often
enough or you've done big batch updates then you may have accumulated lots of
dead tuples and then your vacuum analyze doesn't actually remove them from the
table.

Moreover, if the tables are undergoing updates or deletes then you should
expect to always have some steady state level of dead tuples in the table. Any
tests conducted immediately after a "vacuum full" or "cluster" won't include
that factor.

All that said clustering is indeed often quite effective. Especially if it
makes an index scan efficient enough to win over sequential scans you can see
some huge effects. It's most useful for tables that aren't undergoing lots of
updates and don't need to be reclustered often.

--
greg