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.
--
-Josh BerkusAglio Database SolutionsSan Francisco