Gregory Stark wrote:
The "this day and age" argument isn't very convincing. Hard drive capacityIn point of fact, there haven't been noticeable seek time improvements for years. Transfer rates, on the other hand, have gone through the roof.
growth has far outstripped hard drive seek time and bandwidth improvements.
Random access has more penalty than ever.
Which is why I would question the published tuning advice that recommends lowering it to 2 for arrays. Arrays increase the effective transfer rate more than they reduce random access times. Dropping from 4 to 2 would reflect going from a typical single 7200rpm ATA drive to a 15000rpm SCSI drive, but striping will move it back up again - probably even higher than 4 with a big array (at a guess, perhaps the relationship might be approximated as a square root after allowing for the array type?).
With default settings, I've seen the planner pick the wrong index unless random_page_cost was set to 2. But in testing on an ATA drive, I achieved slightly better plan costings by increasing cpu_tuple_cost (relative to cpu_index_tuple_cost - by default it's only a factor of 10) and actually *raising* random_page_cost to 5! So why pick on one parameter? It's all going to vary according to the query and the data.
I agree with Tom 100%. Pulling levers on a wonky model is no solution.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
