Hi,
I am having some troubles with a select group of tables in a database
which are acting unacceptably slow. For example a table with
approximately < 10,000 rows took about 3,500ms to extract a single row
using the following select statement:
SELECT * FROM table WHERE column = 'value'
I have preformed this same test on a number of different tables, only
a few of which have this same problem. The only common thing that I
can see between these affected tables is the fact that they are dumped
and re-populated every day from an outside source. The temporary
solution that I have found is to copy all the data into another (brand
new) table using:
SELECT * INTO table2 FROM table
After dropping the old table and renaming the new table to reflect the
old table, I run the same tests as before and find that the table
responds much faster (approx. < 10ms). My solution is only addressing
the symptoms of the problem and not the actual root cause. So my
question to everyone is what is really happening? Why are these
tables becoming so slow. The idea that I am leaning towards is the
need to regularly vacuum these tables, which has never been done
before. After switching from other database systems, I am
unaccustomed to the vacuuming concept. Thus, if you believe I am
correct in my believe that this is causing all my problems, can you
please explain vacuuming to me. I have attempted to run a simple
"VACUUM VERBOSE" command on the entire database, with little success
(it comes back saying something along the lines of the need to
increase the max_fsm_pages, which I am unfamiliar with).
Sorry for the long post, but any help with the above would be greatly
appreciated.
Thank you,
Daniel