Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0603A133798E for ; Fri, 12 Nov 2010 06:10:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 57534-06 for ; Fri, 12 Nov 2010 10:10:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id 75AD61336971 for ; Fri, 12 Nov 2010 06:10:05 -0400 (AST) Received: by fxm16 with SMTP id 16so2108769fxm.19 for ; Fri, 12 Nov 2010 02:10:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=sI5BmZMTjZmUuTgR9kC4woN7Hzpg7FxFhnXST53j+hA=; b=MEzSPRSEaZTLMD1s6HhehWl+F7asc64htzEkFAT0YlLujux3BKAEcH47/Wg3Uqw9Qv VDoCdpWbzA0jpyFn62efk3Se340b2kdlVhqLc8UeUiL2i9L2wuOm1j5kb9CXZvFcs2rj iQcB0/WGkwHlbaEaZZRJrmfPuYqoJnaHFoXCs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=h6wF+xTF469TVAgVT9iOGE2HqQhndjxlXW+f+gOPdqgKJCo3ByJ5Isg8k08KR70KCw l2BU9CDn3eALwTfwlP/7tQJlTqI3oGYF8zK1hW1qtb7ei6+3kEYZnZYnpr+x0xkTdtqt x1/LsQJfz28GoBSsPXsOI93We/U2fNEZp8YiE= Received: by 10.223.108.147 with SMTP id f19mr1217733fap.68.1289556604725; Fri, 12 Nov 2010 02:10:04 -0800 (PST) Received: from [10.1.1.57] (skuns.zoral.com.ua [91.193.166.194]) by mx.google.com with ESMTPS id l14sm1260575fan.33.2010.11.12.02.10.02 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 12 Nov 2010 02:10:03 -0800 (PST) Message-ID: <4CDD1279.5090809@gmail.com> Date: Fri, 12 Nov 2010 12:10:01 +0200 From: Vitalii Tymchyshyn User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: anti-join chosen even when slower than old plan 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> <19088.1289501929@sss.pgh.pa.us> <19796.1289504156@sss.pgh.pa.us> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/184 X-Sequence-Number: 41208 I'd say there are two Qs here: 1) Modify costs based on information on how much of the table is in cache. It would be great if this can be done, but I'd prefer to have it as admin knobs (because of plan stability). May be both admin and automatic ways can be followed with some parallel (disableable) process modify knobs on admin behalf. In this case different strategies to automatically modify knobs can be applied. 2) Modify costs for part of table retrieval. Then you need to define "part". Current ways are partitioning and partial indexes. Some similar to partial index thing may be created, that has only "where" clause and no data. But has statistics and knobs (and may be personal bufferspace if they are introduced). I don't like to gather data about "last X percents" or like, because it works only in clustering and it's hard for optimizer to decide if it will be enough to scan only this percents for given query. Best regards, Vitalii Tymchyshyn