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

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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954