Re: BUG #16625: Query Optimizer - Performance bug related to removal of unnecessary aggregate function

Thank you so much for your reply!
Yes, I should have pointed out in the bug report
that "supplier"."s_suppkey" is the primary key and thus the GROUP BY should
be redundant.
I am also attaching the result of "\d supplier" to this email:
tpch5=# \d supplier
Table "public.supplier"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
s_suppkey | integer | | not null |
s_name | character(25) | | not null |
s_address | character varying(40) | | not null |
s_nationkey | integer | | not null |
s_phone | character(15) | | not null |
s_acctbal | numeric(15,2) | | not null |
s_comment | character varying(101) | | not null |
Indexes:
"supplier_pkey" PRIMARY KEY, btree (s_suppkey)
Foreign-key constraints:
"supplier_fk1" FOREIGN KEY (s_nationkey) REFERENCES nation(n_nationkey)
Referenced by:
TABLE "partsupp" CONSTRAINT "partsupp_fk1" FOREIGN KEY (ps_suppkey)
REFERENCES supplier(s_suppkey)

We will make sure we conclude such important context information in our
future bug reports.

On Sat, Sep 19, 2020 at 11:57 AM Christophe Pettus <xof(at)thebuild(dot)com> wrote:

>
>
> > On Sep 18, 2020, at 20:32, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
> [snip]
> > First query:
> > SELECT "s_suppkey"
> > FROM "supplier"
> > WHERE s_suppkey > 100;
> >
> > Second query:
> > SELECT "s_suppkey"
> > FROM "supplier"
> > WHERE s_suppkey > 100
> > GROUP BY s_suppkey;
>
> [snip]
>
> > [Expected Behavior]
> > Since these two queries are semantically equivalent, we were hoping that
> > PostgreSQL will return the same results in roughly the same amount of
> > time.
>
> These two queries are not semantically equivalent, as described. I might
> guess that the table definition has a primary key (or should have had) on
> "supplier"."s_suppkey", and thus the GROUP BY should be redundant, but
> there's nothing in the report or the query plans that indicate this is the
> case.
> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com
>
>

--
-Xinyu