Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jC4vC-000235-Bl for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Mar 2020 17:13:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jC4vA-0000pm-Jt for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Mar 2020 17:13:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jC4gT-0003mD-AP for pgsql-bugs@lists.postgresql.org; Wed, 11 Mar 2020 16:58:17 +0000 Received: from mail-pf1-x441.google.com ([2607:f8b0:4864:20::441]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jC4gR-0005GC-BP for pgsql-bugs@lists.postgresql.org; Wed, 11 Mar 2020 16:58:16 +0000 Received: by mail-pf1-x441.google.com with SMTP id i13so1672163pfe.3 for ; Wed, 11 Mar 2020 09:58:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=vEDWG1tKnbuoxWDkpXkCCOQyxmpummq1vDMCMoeSesc=; b=IlzCV5N75hpKJPiJfv1EE6TZ1GLhAgOZFsaR6JoYXufw+NDVkaMc84yFz7EN2zcsZa VOqnDFYCpi5/EnPhEQ4JgQVK0r6KpNadjx5Tw/tuGh3uQfwuRPwq0dOx/70WTQ4UcF25 hnfRR0eP+cn1XJM6aWmX1afzJnfjKvX5mZAUzkYV7Idexi1Uht0oUy0HHhq52+FkGRnq DmtMtf5yzlLqmZ2B0wOxEZ421Q94QF/hOEYaYdiPj7qsDGwq7HO4LpAPUVumJF7xeNLy ma51FOkhIxSoUbYhPFhA10DUn14QnDWVbJRTnN8+uLq/CQuAq+/5vGs9M2apDrIqXliC yyqg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=vEDWG1tKnbuoxWDkpXkCCOQyxmpummq1vDMCMoeSesc=; b=QMwzrtWZ0dyjlcQt9qZFZNHtBJ3VnoQrhwNdqSDNIeTJdVlXAHBO8rMhtj4MdQfvcm SB63oYFFQ7myZmwNpSyvubPdSOqYZEXYtQCbeBkzqUTwAbbliwkmmxtG16UjIQJRaFWH bRn/hbbKnvyeK8GeO2rSqkHCsIoMuoXQQAWlrDZNRpKIiGBo5AlCaZO8NCW6EF75Y+bD vHbsDzyUXLKHjGAaLASw9lURq8dUvMB11CK9e4jp/oJ9GfcKYoGNMVRxwbEF8AE68n/X TTxoonWsL49LL1c9/lUhw2OCdG3heGJrQL/bju0OE8+ospkekBsASxYS3Bza4FHH4lzI 8NoQ== X-Gm-Message-State: ANhLgQ30sUYJuenERiz9Xv0vv+JgCxopMTGl7uAhUnG1nzfa9IgAW+hP 3soBJhvtt4hH1zfCe7r2SdE= X-Google-Smtp-Source: ADFU+vv0xwI6/kqjO52+/jvVolnjydP1d/6F8bbYU1XmkKWtQs73Hd6yCq3vBdvkO0u4d+e1Zyoaeg== X-Received: by 2002:a63:2447:: with SMTP id k68mr3665419pgk.368.1583945893136; Wed, 11 Mar 2020 09:58:13 -0700 (PDT) Received: from ?IPv6:2603:3023:868:9800:18d4:7167:2d0c:d0f5? ([2603:3023:868:9800:18d4:7167:2d0c:d0f5]) by smtp.gmail.com with ESMTPSA id e18sm6242113pjt.41.2020.03.11.09.58.11 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 11 Mar 2020 09:58:12 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Re: Optimizer Doesn't Push Down Where Expressions on Rollups From: Logan Bowers In-Reply-To: <2865.1583935603@sss.pgh.pa.us> Date: Wed, 11 Mar 2020 09:58:11 -0700 Cc: Richard Guo , pgsql-bugs@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <90491802-76A3-4AE8-AD2E-1646B68016FC@gmail.com> References: <2865.1583935603@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3608.60.0.2.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Just in case it wasn=E2=80=99t obvious from the example, I=E2=80=99m = talking about only cases where the all groups in the grouping set share = a subset of columns in common and those are the columns being = conditioned in the WHERE clause.=20 I get it if it=E2=80=99s not something actionable, but it is a bummer to = see query time explode when going from one grouping set to two grouping = sets. :/=20 Cheers, and I=E2=80=99ve been a big fan of y=E2=80=99alls work for going = on two decades now. Thank you!=20 > On Mar 11, 2020, at 7:06 AM, Tom Lane wrote: >=20 > Richard Guo writes: >> In your case, the WHERE clauses would get pushed down into the = subquery >> for both queries, with/without the ROLLUP. But since the subquery = uses >> grouping/grouping sets, the WHERE clauses would be put in HAVING of = the >> subquery. >=20 > Right, we do successfully push the clauses into HAVING of the = subquery. >=20 >> Then when we plan for the subquery, we will decide whether a HAVING >> clause can be transfered into WHERE. Usually we do not do that if = there >> are any nonempty grouping sets. Because if any referenced column = isn't >> present in all the grouping sets, moving such a clause into WHERE = would >> potentially change the results. >=20 > Yeah. I think that it might be safe if the proposed clause can > be proven strict for (some subset of?) the grouping columns, because > that would eliminate the rollup grouping sets where those columns > come out NULL because they aren't being grouped on. (This could then > also factor into throwing away those grouping sets, perhaps.) >=20 > Anyway, this is not a bug; it's a proposed planner improvement, and > not a trivial one. >=20 > regards, tom lane