I am running pg 7.2 the PG reference build.
Thanks for the ANALYZE tip, it led me to a answer.
This database gets a monthly update and it read only until the next
update. I ANALYZE once after each update. Since the data does not
change I should not need to ANALYZE again afterwards.
I mentioned this to the dbadmin that manages the data and found out one
of the other users UPDATED some of the columns the morning that I was
seeing this behavior.
I'll reANALYZE and see what happens.
Thanks,
-Aaron Held
Josh Berkus wrote:
> Aaron,
>
>
>> # SET enable_seqscan to FALSE ;
>> forced the use of an Index and sped things up greatly.
>>
>>I am not sure why it made the switch. The load on the server seems to
>>affect the performance, but I am seeing it more on the production server
>>with 100 million rows as opposed to the development server with only
>>about 6 million. I need to buy more drives and develop on a larger data
>>set.
>
>
> What version are you using?
>
> I'd have 3 suggestions:
> 1) ANALYZE, ANALYZE, ANALYZE. Then check if the row estimates made by EXPLAIN
> seem accurate.
> 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser
> estimates.
> 3) Test this all again when 7.3 comes out, as parser estimate improves all the
> time.
>