On Sat, 23 Dec 2006, Benjamin Arai wrote:
I thought that you only need to use the -z flag if the distribution of the
data is changing.
You're absolutely correct. Have you not been inserting, updating or deleting
data? It sounds like you are based on the followup email you just sent:
One more note about my problem, when you run a query on older data in the
table then it work great but if you query newer data then is very slow.
Ex.
SELECT * from my_table WHERE date >=12/1/2005 and date <= 12/1/2006; <- slow
SELECT * from my_table WHERE date >=12/1/2002 and date <= 12/1/2003; <- fast
It just has to do with the new data for some reason.
Try and run "ANALYZE my_table;" from psql and see if that makes things faster.
If it does, then you likely need to analyze more often than never. I'm
guessing if you're inserting data that has a date or timestamp, then you
definitely need to be analyzing..that's even one of the examples used in the
docs:
http://www.postgresql.org/docs/8.1/interactive/maintenance.html#VACUUM-FOR-STATISTICS
"For example, a timestamp column that contains the time of row update will
have a constantly-increasing maximum value as rows are added and updated; such
a column will probably need more frequent statistics updates than, say, a
column containing URLs for pages accessed on a website. The URL column may
receive changes just as often, but the statistical distribution of its values
probably changes relatively slowly."
Jeff Frost wrote:
On Sat, 23 Dec 2006, Benjamin Arai wrote:
The largest table in my database (30GB) has mysteriously went from taking
milli-seconds to perform a query to minutes. This disks are fine and I
have a 4GB shared_memory. Could this slow down have to do with the
fsm_max_pages or something else like that? I made it larger but the
queries still taking a long time. I do daily vacuum's but I don't run it
with -z or --full. I would like to avoid doing a --full if possible
because it would literally take over a week to complete. Any help would
be greatly appreciated.
Benjamin,
When is the last time you ran ANALYZE? That's what the -z option does. If
you're only vacuuming once daily, you should definitely analyze with the -z
flag as well.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
