Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i61AJ-0000rI-Me for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Sep 2019 23:27:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i61AI-0006Wh-Ha for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Sep 2019 23:27:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i61AI-0006WZ-6y for pgsql-bugs@lists.postgresql.org; Thu, 05 Sep 2019 23:27:46 +0000 Received: from mail-qt1-x844.google.com ([2607:f8b0:4864:20::844]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i61AF-0006Tf-94 for pgsql-bugs@lists.postgresql.org; Thu, 05 Sep 2019 23:27:45 +0000 Received: by mail-qt1-x844.google.com with SMTP id v11so4915730qto.13 for ; Thu, 05 Sep 2019 16:27:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:mime-version:content-disposition :content-transfer-encoding:in-reply-to:user-agent; bh=MYoT65SeV2OAIbkQPVho5xRAKoxTsLZqBzpYDdXjqhI=; b=TvDMU5ceOTOVhbFxpAsSxumwH0OjxBWEen7qCMQ6jt1IULFin5k6L/H5GTxtjhPWOm 3YFb0fnVnWR1G0YvUdGrQIy2Hn1gJ+I5/WFOc8XwCohQSIyeyqdw1WSfG+rUccKRVNAo pcLZOAmdys7AM/JIAS6qJrF4BKlY/BrEbjODiFY0zMKqOSUludSIoYddkJZr5uy2WQj1 en4ctWlsefUJdVe9UYwam/ELZcQlYViOr0IYf1s8J1TSKo8WGGhljBX8rmJAbgx7O5V/ WKwVoN0JuqeSmaolvwtxdMHLV/EHV3siyVMibVICN/qsK63xAwIIgdHvK7OjNiAyjXpn gC4g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:mime-version :content-disposition:content-transfer-encoding:in-reply-to :user-agent; bh=MYoT65SeV2OAIbkQPVho5xRAKoxTsLZqBzpYDdXjqhI=; b=MwCfnmSJkzQzXmybphcCh3BJp3Ng7J4XXvVZO3/n1ZFYOVHILvInXGkpZVfxzWaTFJ Ojc6mzC+0plmdBee/w1zysP3Q+0wvq6+oewQvD9KJhUZTeOnj3k1HpqHlfOok5+eokZr KmVkKa8EGn0wW4ez75/51lXxrHXImcxzHwaI/6+Rr8hNz6mgvdOA22/YcxXwlAeJR8Dz Z5pK9CVskezMNWrOPsJnWwzF/KFayGhngJd/MJRaI38bjjkE/Q//AOsLXQaLE7APal3O Uxx5sTfN2k+emKrWt54KwnHk+iViC6EeRwXWErYADlGnDpOIRsLA8PIQgB4YrmHLr9+o beuw== X-Gm-Message-State: APjAAAXYIrk32WBZjZUa88moAcBFz3wVwI12queWorTaCHrF2D3+dEbJ wzRg4wVDvWbWPVrzrXHE5NK9N0LjrwU= X-Google-Smtp-Source: APXvYqw70QjP84ZXvXrPkyI+Pa1EDNUkp7kBBEatlEl1GHSKYJqxd5cFYRqkWiC+Y/y+judVacoP+Q== X-Received: by 2002:a05:6214:32f:: with SMTP id j15mr3778263qvu.42.1567725617064; Thu, 05 Sep 2019 16:20:17 -0700 (PDT) Received: from nimloth.alvh.no-ip.org ([190.121.29.3]) by smtp.gmail.com with ESMTPSA id i10sm1565508qtm.4.2019.09.05.16.20.15 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 05 Sep 2019 16:20:16 -0700 (PDT) Received: by nimloth.alvh.no-ip.org (Postfix, from userid 1000) id 49CEF120BB0; Thu, 5 Sep 2019 19:20:14 -0400 (-04) Date: Thu, 5 Sep 2019 19:20:14 -0400 From: Alvaro Herrera To: David Rowley Cc: Tom Lane , Marko Tiikkaja , PostgreSQL mailing lists Subject: Re: BUG #15383: Join Filter cost estimation problem in 10.5 Message-ID: <20190905232014.GA32014@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2019-Jul-01, David Rowley wrote: > One thing we could look at would be to charge an additional > cpu_tuple_cost per outer row for all joins except semi, anti and > unique joins. This would account for the additional lookup for > another matching row which won't be found and cause the planner to > slightly favour keeping the unique rel on the inner side of the join, > when everything else is equal. So does this proposal fix the problem that David was describing earlier? If it does, Tom, would you like to get this finalized and pushed? I've bcc'ed someone who might be interested in chiming in and/or helping out. Please look here: https://postgr.es/m/153683552113.22350.18441286362867559841@wrigleys.postgresql.org Thanks, -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services