Search Postgresql Archives

Re: [ADMIN] Problem with large table not using indexes (I think)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux