X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 2D2F930DBB2 for ; Fri, 13 Jun 2003 14:07:53 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 40445-05 for ; Fri, 13 Jun 2003 11:07:42 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 7218830D18F for ; Fri, 13 Jun 2003 11:07:42 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5DE7gU6000408; Fri, 13 Jun 2003 10:07:42 -0400 (EDT) To: Vincent van Leeuwen Cc: pgsql-performance@postgresql.org Subject: Re: tweaking costs to favor nestloop In-reply-to: <20030613123922.GX1535@md2.mediadesign.nl> References: <20030611193313.GE1535@md2.mediadesign.nl> <23557.1055362673@sss.pgh.pa.us> <20030613123922.GX1535@md2.mediadesign.nl> Comments: In-reply-to Vincent van Leeuwen message dated "Fri, 13 Jun 2003 14:39:22 +0200" Date: Fri, 13 Jun 2003 10:07:42 -0400 Message-ID: <407.1055513262@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/59 X-Sequence-Number: 2143 Vincent van Leeuwen writes: > How did you calculate the value of 3? Estimated cost of an indexscan is approximately proportional to random_page_cost, but cost of a seqscan isn't affected by it. You had a hash join plan that used two seqscans (so its estimated cost is unaffected by random_page_cost) plus a merge join plan that had one indexscan input. I just extrapolated the change in the indexscan cost needed to make the ratio of total costs agree with reality. This is a pretty rough calculation of course, but I don't believe small values of random_page_cost except for situations where all your data is buffered in RAM. It's real easy to get led down the garden path by small test cases that get fully buffered (especially when you repeat them over and over), and pick cost values that will not reflect reality in a production environment. I can't say whether that actually happened to you, but it's something to be on your guard about. > Another problem we've noticed is that on an idle database certain queries are > better off using an indexscan than a seqscan, something which the planner > already wanted to do. But when the load on the database gets a lot higher, > indexscans are consistently slower than seqscans (same query, same > parameters). See above. Increasing load reduces the chances that any one query will find its data already buffered, since there's more competition for the available buffer space. > Does 7.4 already have changes in this area that will affect this query? No. regards, tom lane