Wondering if someone could explain a pecularity for me:We have a database which takes 1000ms to perform a certain query on. If I pg_dump that database then create a new database (e.g. "tempdb") and upload the dump file (thus making a duplicate) then the same query only takes 190ms !! Vacuum, vacuum analyse, and vacuum full analyse does not seem to have an impact on these times. Can anyone explain why this may be occurring and how I might be able to keep the original database running at the same speed as "tempdb"? Thanks in advance, Dave.
What version of postgres? There are a few possibilities. If you are having a lot of updates to the table, you can get index bloat. And vacuum doesn't fix indexes. You have to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM FULL has, so you need to be a little careful with it. Probably better is to do CLUSTER, as it does a REINDEX and a sort, so your table ends up nicer when you are done. Also, older versions of postgres had a worse time with index bloat. One thing that caused a lot of problem is a table that you insert into over time, so that all the values are incrementing. If you are deleting older entries, that area won't be re-used because they fall at the back end. I believe newer versions have been fixed. By the way, I think doing: CREATE DATABASE tempdb WITH TEMPLATE = originaldb; Is a much faster way of doing dump and load. I *think* it would recreate indexes, etc. If it just does a copy it may not show the dump/restore improvement. John =:->
Description: OpenPGP digital signature