Kevin Murphy <murphy@xxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
