Re: [HACKERS] <> join selectivity estimate question - Mailing list pgsql-hackers

On Fri, Mar 17, 2017 at 1:54 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
>  SELECT *
>    FROM lineitem l1
>   WHERE EXISTS (SELECT *
>                   FROM lineitem l2
>                  WHERE l1.l_orderkey = l2.l_orderkey);
>
>  -> estimates 59986012 rows, actual rows 59,986,052 (scale 10 TPCH)
>
>  SELECT *
>    FROM lineitem l1
>   WHERE EXISTS (SELECT *
>                   FROM lineitem l2
>                  WHERE l1.l_orderkey = l2.l_orderkey
>                    AND l1.l_suppkey <> l2.l_suppkey);
>
>  -> estimates 1 row, actual rows 57,842,090 (scale 10 TPCH)

The relevant code is in neqsel().  It estimates the fraction of rows
that will be equal, and then does 1 - that number.  Evidently, the
query planner thinks that l1.l_suppkey = l2.l_suppkey would almost
always be true, and therefore l1.l_suppkey <> l2.l_suppkey will almost
always be false.  I think the presumed selectivity of l1.l_suppkey =
l2.l_suppkey is being computed by var_eq_non_const(), but I'm a little
puzzled by that function is managing to produce a selectivity estimate
of, essentially, 1.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company