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 1lEZMM-0003yi-7D for pgsql-bugs@arkaria.postgresql.org; Tue, 23 Feb 2021 15:12:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lEZML-0003X5-3B for pgsql-bugs@arkaria.postgresql.org; Tue, 23 Feb 2021 15:12:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEZMK-0003Wy-TJ for pgsql-bugs@lists.postgresql.org; Tue, 23 Feb 2021 15:12:20 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEZMH-00057d-Mx for pgsql-bugs@lists.postgresql.org; Tue, 23 Feb 2021 15:12:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 11NFCDVr3821999; Tue, 23 Feb 2021 10:12:14 -0500 From: Tom Lane To: liuxy@gatech.edu cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16888: Reoccurring group by clauses makes system slower In-reply-to: <16888-05b803340050f901@postgresql.org> References: <16888-05b803340050f901@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Tue, 23 Feb 2021 02:28:15 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3821997.1614093133.1@sss.pgh.pa.us> Date: Tue, 23 Feb 2021 10:12:13 -0500 Message-ID: <3821998.1614093133@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > We have 2 TPC-H queries which fetch the same tuples but have significant > query execution time differences (3.8 times). > ... > Since these two queries are semantically equivalent, we were hoping that > PostgreSQL will evaluate them in roughly the same amount of time. It looks > to me that the query optimizer does not realize the inner GROUP BY clause is > unnecessary given the outer GROUP BY clause. TBH, you are wasting your time reporting these sorts of things as bugs. They are not bugs, unless equivalent queries give different answers. At most, they represent optimizations nobody has bothered to put in. In cases where the problem is really a visibly-badly-written query (e.g., redundant GROUP BY clauses, as here), it's unclear that we'd accept such an optimization if it were proposed, because adding checks for such cases would necessarily penalize well-written queries to benefit badly-written ones. The overhead would have to be extremely small, or else the change would need to have some other benefit that accrues to well-written queries as well as badly-written ones. regards, tom lane