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 1moy3I-00059G-CA for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Nov 2021 01:23:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1moy3G-0004bn-MH for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Nov 2021 01:23:22 +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 1moy3G-0004bd-8i for pgsql-hackers@lists.postgresql.org; Mon, 22 Nov 2021 01:23:22 +0000 Received: from mail-il1-x133.google.com ([2607:f8b0:4864:20::133]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1moy3D-0000Cz-2x for pgsql-hackers@postgresql.org; Mon, 22 Nov 2021 01:23:21 +0000 Received: by mail-il1-x133.google.com with SMTP id w4so5265120ilv.12 for ; Sun, 21 Nov 2021 17:23:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=L5iWHpNqjl+oCC3AWYOhQaiEnscnriMxJqjKouScJgw=; b=CBdQOAzuWtP+J0iHFBR/D8LKKsUf0wgvBbwU7P01CxHcUcEmWaiL19sJC42bSKhLXw r4qMaeeb7ZcmSsz+cCFn6r5VAL8Nm3PV7H91HHXwFUHYcbPy48fr8uDUMFZpawrturR7 7pUu9Z8oIYAhgkJfu0zeNasnB6guJTEXGQc03t3nLg/ezdAdIl4b8Cv6nQLcG+DoMNrl Tvr7pWOL253vPbUBFU7uybXQrTsWC5dx2+VA37+ebIkQfoLK9jMXL9rmsy+4RtcSbQ68 eOIqScjtAmxhagsZuEaO9v7WYsBWwtyLAPnZBFCqpx93cpyR2Qw6NOABzbQQnlYYBwAg Djuw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=L5iWHpNqjl+oCC3AWYOhQaiEnscnriMxJqjKouScJgw=; b=A7htuNDXlwzGtOHSodRFGAt88plvwnapYyIcIUi88fYDUXsGhR0MMELqNu0PSIj3pN xUMctx/jJ6QgpjZymM8oBVy2wJeTcGf9QE1zpRynJmOE4jNJdS/w3OmO6MF56nF91w82 Od+9APLPKxKmjXgBvoEcBezPqOQMtHqZoN6j35EJL5C406hsx5tXezQiVxrMZs2/RwPy UAFFe6fgJkhiquE/sFoqEUcGbrqA2YcWL+gKPIe7wwoumkHlKbSy0Ir+4FIXPRIkHBuI vquWMYoqeY2fzu5ZZPIJpZDuEJnea0GT4u7Oj5yhWN1Hp6bywRc+sdKW2IO6zfI4V27m mg6g== X-Gm-Message-State: AOAM533X9shPlRMIhijJ2UzrqHRFOEKmEKE2ORVbhHMA+Jfts6UAzo1M YCf0LhThRO5tI9oQSsaLToxnrA== X-Google-Smtp-Source: ABdhPJwH9h96dRq3BB4vGUBJqprHuduBWUgE0oXbgpXeOv2LETeUUQTfBJsimu/4TfaV7FpwbS3Gag== X-Received: by 2002:a92:dc8d:: with SMTP id c13mr17125896iln.75.1637544196918; Sun, 21 Nov 2021 17:23:16 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id t6sm1037910ios.13.2021.11.21.17.23.16 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 21 Nov 2021 17:23:16 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id A65AC800D76; Sun, 21 Nov 2021 19:23:15 -0600 (CST) Date: Sun, 21 Nov 2021 19:23:15 -0600 From: Justin Pryzby To: Tomas Vondra Cc: pgsql-hackers@postgresql.org Subject: Re: using extended statistics to improve join estimates Message-ID: <20211122012315.GH17618@telsasoft.com> References: <2ed75657-e084-9539-c6de-597e5675014c@enterprisedb.com> 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: Archived-At: Precedence: bulk Your regression tests include two errors, which appear to be accidental, and fixing the error shows that this case is being estimated poorly. +-- try combining with single-column (and single-expression) statistics +DROP STATISTICS join_test_2; +ERROR: statistics object "join_test_2" does not exist ... +ERROR: statistics object "join_stats_2" already exists -- Justin