On Sep 21, 2010, at 2:34 PM, Ogden wrote: > > On Sep 21, 2010, at 2:16 PM, Greg Smith wrote: > >> Joshua D. Drake wrote: >>> PostgreSQL's defaults are based on extremely small and some would say >>> (non production) size databases. As a matter of course I always >>> recommend bringing seq_page_cost and random_page_cost more in line. >>> >> >> Also, they presume that not all of your data is going to be in memory, and >> the query optimizer needs to be careful about what it does and doesn't pull >> from disk. If that's not the case, like here where there's 8GB of RAM and a >> 7GB database, dramatic reductions to both seq_page_cost and random_page_cost >> can make sense. Don't be afraid to think lowering below 1.0 is going too >> far--something more like 0.01 for sequential and 0.02 for random may >> actually reflect reality here. >> > > I have done just that, per your recommendations and now what took 14 seconds, > only takes less than a second, so it was certainly these figures I messed > around with. I have set: > > seq_page_cost = 0.01 > random_page_cost = 0.02 > cpu_tuple_cost = 0.01 > > Everything seems to run faster now. I think this should be fine - I'll keep > an eye on things over the next few days. > > I truly appreciate everyone's help. > > Ogden >
I spoke too soon - well I came in this morning and reran the query that was speeded up yesterday by a lot after tweaking those numbers. This morning the first time I ran it, it took 16 seconds whereas every subsequent run was a matter of 2 seconds. I assume there is OS caching going on for those results. Is this normal or could it also be the speed of my disks which is causing a lag when I first run it (it's RAID 5 across 6 disks). Is there any explanation for this or what should those settings really be? Perhaps 0.01 is too low? Thank you Ogden -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
