Tom Lane wrote:
Have you checked to see whether you are encountering table or index
bloat? Your vacuuming policy doesn't sound unreasonable on its face,
but there's no substitute for actually looking at physical file sizes
when you aren't sure what's wrong. Try doing a pg_dump and reload into
a virgin database, then compare file sizes with the original
table-by-table to see if anything is drastically out of line.
Don't forget to look at the system catalogs too.
I dumped and loaded then wrote a script to compare the sizes of tables.
I noticed that a few of the indexes, especially the indexes on the
15million table shrunk a lot (~50% reduction - ~400Mb). Also, the
pg_attribute_relid_attnam_index index shrank loads, to 1% of its
original size. From this, we tried reindexing indexes and found we got
the same reduction in index size. Strangely though, our queries appear
to have sped up not just on the servers we reindexed, but across the
whole cluster - so apparently something else is affecting the speed of
this all, not just bloat. We're scratching our heads, this is nothing if
not a little frustrating.
--
David Mitchell
Software Engineer
Telogis
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
