Hi Jeff > It kind of does. The expected speed is predicated on the number of rows being 200 fold higher. If the number of rows actually was that much higher, the two speeds might be closer together. That is why it would be interesting to see a more typical case where the actual number of rows is closer to the 2000 estimate. Ah, I see of course. Makes a lot of sense when you think about it. This has been quite an enlightening adventure into the guts of postgres for me :) > But I am curious about how the cost estimate for the primary key look up is arrived at: ( Delt with in your next reply, thanks for figuring that out! I will certainly try the patch) > I've heard good things about Greg Smith's book, but I don't know if it covers this particular thing. A copy is on its way, thank you. >> We are in the rather pleasant situation here in that we are willing to >> spend money on the box (up to a point, but quite a large point) to get >> it up to the spec so that it should hardly ever need to touch the >> disk, the trick is figuring out how to let our favourite database server know that. > Well, that part is fairly easy. Make random_page_cost and seq_page_cost much smaller than their defaults. Like, 0.04 and 0.03, for example. Yes, I have been playing a lot with that it makes a lot of difference. When I tweak them down I end up getting a lot of nested loops instead of hash or merge joins and they are much faster (presumably we might have gotten a nested loop out of the planner if it could correctly estimate the low number of rows returned). I've got plenty of ammunition now to dig deeper, you guys have been invaluable. Cheers, Phil -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
