Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jaJO8-00057R-Li for pgsql-performance@arkaria.postgresql.org; Sun, 17 May 2020 13:31:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jaJO7-0003In-Iu for pgsql-performance@arkaria.postgresql.org; Sun, 17 May 2020 13:31:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jaJO7-0003If-6p for pgsql-performance@lists.postgresql.org; Sun, 17 May 2020 13:31:31 +0000 Received: from mail-qt1-x841.google.com ([2607:f8b0:4864:20::841]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jaJO4-0006OA-Qj for pgsql-performance@postgresql.org; Sun, 17 May 2020 13:31:30 +0000 Received: by mail-qt1-x841.google.com with SMTP id n22so3510129qtv.12 for ; Sun, 17 May 2020 06:31:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=ywGVNBnBu0pcRDNSy/8FMTdIo/+zGCCP6Xkwju5j2O8=; b=LT/ywbVnBlbCPsv/0a3d5Wre2nGDSlW3f6er+hD0ck4m/lMDt17KLtsXmwU6XMAuCg X6OmVP3KgIKW6Q1ZmXUrrTLopsAOOgrjPabla/ZxJJFTCTdq29Gy0qfTaVGIzHltoUYw hJPx3v5OoH5XdH2baEYxQCA2WTDEw84Ov0CJw1d4voXgVkxH/y0PfRSJ4OKCnASrrwa0 phLuF/3t0rfM7x4RsMKhNoyhdQv2jd40NEnMlqDKZeBc7SeWwhqe+16oGgDAET8w8WC1 tkaJe5gwb+0vnrapLTP7XLfOcG2RiVy0b3VJeBR/NlrMPgGohMWtgxNZySswe1P10x65 JeNA== 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:references :mime-version:content-disposition:in-reply-to:user-agent; bh=ywGVNBnBu0pcRDNSy/8FMTdIo/+zGCCP6Xkwju5j2O8=; b=HHsZ8uCo6DkaxufH1HzjdhgT5pl+LzJZfiodnrnuxMlP7AfsMg3rjRo1DfezkysJmO IAcyqZZFh6jMvJmxtTwUwp5gzFByMiq1QQQMZJGwvvR4MzitTYPiXqGDY9WwlwsbspXC J7XX4FPDrAfW5H6hqyp472qOSv8YN1kNSoAjur7tPLG6ukqPDEz87IJWjpoad2f+aLQ2 yGtL88JZZ2PGGWyhipvZlBBM+tvTA9kPAZQjm0JWiEGCCo9EZNBcBUaF9fDxsEbetlAW 6tFD5+va/RJSnZZhuXVJ7b6ezoHaxO2V1fJ20U/KIlyBVFug/0BPXUWqdqGF6Xk47yOU DQ0g== X-Gm-Message-State: AOAM5310y578rbmT81ohlMjeDwaqzJSO4gOtkFu2qEI+FC6BzhGjUq4o ENfDA+Be3mWCvMhdT5qknoteAg== X-Google-Smtp-Source: ABdhPJweiD9qbNdT59jZU19rOXR8aGicbQeMh8tEdCiAWX/cSUy2sZpb4peZG06dvA04okQ2M1ZVlg== X-Received: by 2002:aed:2941:: with SMTP id s59mr9400549qtd.385.1589722286915; Sun, 17 May 2020 06:31:26 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id p2sm5949076qkm.41.2020.05.17.06.31.25 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 17 May 2020 06:31:25 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id 949B18006BB; Sun, 17 May 2020 08:31:24 -0500 (CDT) Date: Sun, 17 May 2020 08:31:24 -0500 From: Justin Pryzby To: Ranier Vilela Cc: pgsql-performance@postgresql.org, hippysoyboy@gmail.com, postgres@binarykeep.com Subject: Re: Plan not skipping unnecessary inner join Message-ID: <20200517133124.GB18456@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 Sun, May 17, 2020 at 09:32:47AM -0300, Ranier Vilela wrote: > I redid the same tests with vanila postgres and with empty tables. > I'm surprised, why does the plan have 2550 rows in explain? That's the *estimated* rowcount. The planner tends to ignore table statistics which say the table is empty, since that can lead to a terrible plan if it's not true (stats are out of date or autovacuum threshold not hit). See also here https://www.postgresql.org/message-id/20171110204043.GS8563%40telsasoft.com -- Justin