Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3FE839FB2F3; Mon, 6 Aug 2007 19:23:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 81989-07; Mon, 6 Aug 2007 19:23:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 214759FB1CA; Mon, 6 Aug 2007 19:23:38 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l76MNY4I003570; Mon, 6 Aug 2007 18:23:34 -0400 (EDT) To: Henrik Zagerholm cc: Gregory Stark , pgsql-performance@postgresql.org, pgsql-general@postgresql.org Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations. In-reply-to: <3F198A58-3132-4B8E-8E2D-1D158C147EA3@mac.se> References: <0C963119-103A-4EB1-B9D8-0FD3E16A11F0@mac.se> <87abt4n75m.fsf@oxford.xeocode.com> <27852.1186414311@sss.pgh.pa.us> <3F198A58-3132-4B8E-8E2D-1D158C147EA3@mac.se> Comments: In-reply-to Henrik Zagerholm message dated "Mon, 06 Aug 2007 23:29:45 +0200" Date: Mon, 06 Aug 2007 18:23:34 -0400 Message-ID: <3569.1186439014@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/332 X-Sequence-Number: 118181 Henrik Zagerholm writes: > At what point does the planner choose seq scans? When it thinks it's cheaper than the other way. There's no hard and fast answer. The immediate problem you've got is that the estimated size of the tbl_file/tbl_filetype_suffix join is off by a factor of almost 20 (8061 vs 486). The plan that you think would be faster involves an inner indexscan on the larger table for each result row from that join, and therefore this error translates directly to a 20x overestimate of its cost, and therefore the planner avoids that in favor of a hash join that indeed is more efficient when there are lots of rows to be joined. It may well be that you also need to adjust random_page_cost and/or effective_cache_size so that the planner's estimates of indexscan vs seqscan costs are more in line with reality on your machine. But it's a capital error to tinker with those numbers on the basis of an example where the rowcount estimates are so far off. (Actually I'd not advise changing them on the basis of *any* single test case, you need to look at average behaviors. Get the count estimates fixed first and then see where you are.) It's also not impossible that the planner is right and the seqscan is better than a lot of index probes ... regards, tom lane