Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9A9201337B8D for ; Thu, 11 Nov 2010 15:41:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 20047-08 for ; Thu, 11 Nov 2010 19:41:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 33A051336971 for ; Thu, 11 Nov 2010 15:41:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oABJfoNs019897; Thu, 11 Nov 2010 14:41:50 -0500 (EST) To: "Kevin Grittner" cc: "Robert Haas" , "pgsql-performance@postgresql.org" , "Mladen Gogala" Subject: Re: anti-join chosen even when slower than old plan In-reply-to: <4CDBEC8B02000025000375CF@gw.wicourts.gov> References: <4CD9663502000025000374B1@gw.wicourts.gov> <20961.1289347680@sss.pgh.pa.us> <4CDA62C60200002500037512@gw.wicourts.gov> <4CDACBBF020000250003755B@gw.wicourts.gov> <4CDB7BAA.7020506@vmsinfo.com> <4CDBB0A40200002500037571@gw.wicourts.gov> <18450.1289499783@sss.pgh.pa.us><18450.1289499783@sss.pgh.pa.us> <19088.1289501929@sss.pgh.pa.us> <4CDBEC8B02000025000375CF@gw.wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Thu, 11 Nov 2010 13:15:55 -0600" Date: Thu, 11 Nov 2010 14:41:50 -0500 Message-ID: <19896.1289504510@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/166 X-Sequence-Number: 41190 "Kevin Grittner" writes: > Besides the "fully-scanned object size relative to relation size > costing adjustment" idea, the only one which seemed to be likely to > be useful for this sort of issue was the "costing factors by user > ID" idea -- the interactive queries hitting the well-cached portion > of the tables are run through a read-only user ID, while the weekly > maintenance scripts (obviously) are not. With the settings I > initially had assigned to the cluster the maintenance scripts would > never have seen this issue; it was tuning to resolve end-user > complaints of slowness in the interactive queries which set up the > conditions for failure, and if I'd had per-user settings, I probably > would have (and definitely *should* have) used them. Erm ... you can in fact do "ALTER USER SET random_page_cost" today. As long as the settings are GUC parameters we have quite a lot of flexibility about how to control them. This gets back to my earlier point that our current form of per-relation properties (reloptions) is considerably less flexible than a GUC. I think that if we create any strong planner dependence on such properties, we're going to end up needing to be able to set them in all the same ways you can set a GUC. regards, tom lane