Alistair Bayley <alistair@xxxxxxxxxxx> writes: > On 18 February 2014 14:40, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> I notice though that the cost estimate for the seqscan plan isn't all that >> much lower than that for the indexscan plan. Probably lowering >> random_page_cost a bit would change the planner's mind. We have no >> information about total size of database vs available RAM, but if it's >> a mostly memory-resident database then such a change would be a good idea. > [ database size is 3GB, RAM 2GB ] The usual advice for database-in-RAM scenarios is to set random_page_cost = 1, or even to lower both random_page_cost and seq_page_cost below 1. In this case, since it's not going to be entirely RAM-resident, a compromise setting around 2 might be a good idea. > I'm particularly interested in the massive different between cost and > actual for the index plan. The seq scan plan has 451984/248694 (ratio > 1.82) for cost/actual, while the index plan has 502051/11597 (ratio > 43.29). At least the seq scan plan is only out by a factor of ~2. Most likely this means that the index plan is taking a lot more advantage of locality-of-reference than the planner is giving it credit for. I wouldn't put too much faith in those numbers by themselves though, because that's what nearly always happens if you run the same case through EXPLAIN more than once: all the data it needs is already in cache. It's a good idea to pay attention to what happens when the plan does have to read in some new data. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
