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 1jBbdw-0004Xi-Pi for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Mar 2020 09:57:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jBbdu-0005zV-U1 for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Mar 2020 09:57:42 +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 1jBPag-0001iQ-Fu for pgsql-bugs@lists.postgresql.org; Mon, 09 Mar 2020 21:05:34 +0000 Received: from mail-pj1-x1043.google.com ([2607:f8b0:4864:20::1043]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jBPae-0000BU-30 for pgsql-bugs@lists.postgresql.org; Mon, 09 Mar 2020 21:05:34 +0000 Received: by mail-pj1-x1043.google.com with SMTP id l41so455669pjb.1 for ; Mon, 09 Mar 2020 14:05:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:mime-version:subject:message-id:date:to; bh=LlAkJd6TRHd/I18KOZQixu6z8Njk7iiknn3wAOUmKco=; b=tfAvABrL+SR8YqRik0AA2pfmj8TKCHoFo1wUszHsOdAouP7r0NbgcOkDIZSC1IOBto g5daVE1NjiDePS5p9CE9iauHQCD7QKKr1airKeOSek3N+huC9SvnKu0vcGx88uh9e+hk 5kcf/+XXYOCFb+yvBiICLZDd3Mp4Z7F6bvgmlKnGBNdDN76zuqjpAdTjnhSOtHzVIm8i xWHAlERwgVnWmuF+ofqtRhoyixStWhVB32jCgPN6zVW6Mdg1duyQE6dHdp1Rja5j814w vZ3nNcZ3/G6xLuzJdV/CtIgLFnA4NE2ooqbxrDXwGTzhIzLUJUJELTizN3bxJkHEIItp nsAg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:mime-version:subject:message-id:date:to; bh=LlAkJd6TRHd/I18KOZQixu6z8Njk7iiknn3wAOUmKco=; b=iEIYpAILs/o1RG8jeGazBKMjh3jRjbV9zFUKlfyu6o3hNsspuMRAvMLNIaQebwt6/4 XEEBdBa2b1Os93pzHRKvEsTFE5vozVR4Ysjf0WUxtrYKtHIRq7epl0Z/pomHTXeh7aYY nQVQvb4NEfBsk4AyilbqY4ayZwa83aiji9oaEIJ/Z4qh3qnXaIEIz01foAfJHRLBmtZS rC08tmjGtaiZ/a1hH0Gf7a5RQ1o6D/Xn6oVIrRA/7ddTe06LJ4EhdKAAfk0B94y+BrT9 cPGzAEmMUBayqI8c4UPKiNk0E0qucJtjp7bptSML8kTwHxQilbpoDdTZauNrs2IVmHYi vFnw== X-Gm-Message-State: ANhLgQ3XN2YJ7v/QFWzxClNqRLwCBOSOgCnEdcJwGifm3ZBe+no9vGT7 DvSP2LC9XPicLNMnhOVdIynWFBcpQ3w= X-Google-Smtp-Source: ADFU+vvvN5+MCrkpke1WF4U9AEL1uoCq1Pv9ga7D5A1BjKJQ8FybY2TumfG+qCTXd7QospMMhhcrJA== X-Received: by 2002:a17:902:8688:: with SMTP id g8mr17310053plo.277.1583787929455; Mon, 09 Mar 2020 14:05:29 -0700 (PDT) Received: from ?IPv6:2603:3023:868:9800:b878:7d6d:4f50:868f? ([2603:3023:868:9800:b878:7d6d:4f50:868f]) by smtp.gmail.com with ESMTPSA id g16sm45955795pgb.54.2020.03.09.14.05.28 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 09 Mar 2020 14:05:28 -0700 (PDT) From: Logan Bowers Content-Type: multipart/alternative; boundary="Apple-Mail=_B3D63D3E-19FC-4386-B7FF-8B5E118DACEC" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Optimizer Doesn't Push Down Where Expressions on Rollups Message-Id: <17F738BE-8D45-422C-BAD0-ACA3090BF46D@gmail.com> Date: Mon, 9 Mar 2020 14:05:27 -0700 To: pgsql-bugs@lists.postgresql.org X-Mailer: Apple Mail (2.3608.60.0.2.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_B3D63D3E-19FC-4386-B7FF-8B5E118DACEC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hello,=20 I=E2=80=99m running into a performance issue where I=E2=80=99m trying to = introduce a rollup into GROUP BY clause. I believe the query planner is = missing an optimization when grouping by multiple terms.=20 Here=E2=80=99s an example that replicates the problem:=20 BEGIN; CREATE TABLE limitation_demo (c1 integer, c2 integer, c3 integer, c4 = integer); INSERT INTO limitation_demo SELECT (random() * 20)::integer, (random() * 20)::integer, (random() * 20)::integer, (random() * 20)::integer FROM generate_series(0,10000) AS foo; CREATE INDEX idx1 ON limitation_demo (c1, c2, c3); --Good EXPLAIN SELECT * FROM (SELECT c1,c2,c3, sum(c4) FROM limitation_demo = GROUP BY c1,c2,c3) AS foo WHERE c1 =3D 1 AND c2 =3D 5 ; --Bad EXPLAIN SELECT * FROM (SELECT c1,c2,c3, sum(c4) FROM limitation_demo = GROUP BY c1, c2, ROLLUP(c3)) AS foo WHERE c1 =3D 1 AND c2 =3D 5; ROLLBACK; Here are the respective query plans:=20 QUERY PLAN = --------------------------------------------------------------------------= --------- GroupAggregate (cost=3D0.29..8.32 rows=3D1 width=3D20) Group Key: limitation_demo.c1, limitation_demo.c2, limitation_demo.c3 -> Index Scan using idx1 on limitation_demo (cost=3D0.29..8.30 = rows=3D1 width=3D16) Index Cond: ((c1 =3D 1) AND (c2 =3D 5)) (4 rows) QUERY PLAN = --------------------------------------------------------------------------= -- HashAggregate (cost=3D255.02..360.03 rows=3D2 width=3D20) Hash Key: limitation_demo.c1, limitation_demo.c2, limitation_demo.c3 Hash Key: limitation_demo.c1, limitation_demo.c2 Filter: ((limitation_demo.c1 =3D 1) AND (limitation_demo.c2 =3D 5)) -> Seq Scan on limitation_demo (cost=3D0.00..155.01 rows=3D10001 = width=3D16) (5 rows) Despite being semantically equivalent, I believe, the latter plan with = the rollup does not use the index. I believe what is happening is that = the WHERE clause is getting pushed down into the inner query without the = ROLLUP, but is not with the ROLLUP.=20 This is a simplified example. In my real-world use case, the inner query = is a view, so I don=E2=80=99t have the option of moving the where = clause.=20 Can y=E2=80=99all let me know if I should submit this somewhere else? = Thanks!=20 --Apple-Mail=_B3D63D3E-19FC-4386-B7FF-8B5E118DACEC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Hello, 
I=E2=80=99m running into a performance = issue where I=E2=80=99m trying to introduce a rollup into GROUP BY = clause. I believe the query planner is missing an optimization when = grouping by multiple terms. 

Here=E2=80=99s an example that = replicates the problem: 

BEGIN;
CREATE TABLE limitation_demo (c1 integer, = c2 integer, c3 integer, c4 integer);

INSERT INTO limitation_demo SELECT
    (random() * 20)::integer,
    (random() * 20)::integer,
    (random() * 20)::integer,
    (random() * 20)::integer
  FROM generate_series(0,10000) AS foo= ;

CREATE INDEX = idx1 ON limitation_demo (c1, c2, c3);

--Good
EXPLAIN SELECT * FROM (SELECT c1,c2,c3= , sum(c4) FROM limitation_demo GROUP = BY c1,c2,c3) AS foo  WHERE c1 = =3D 1 AND c2 =3D 5 ;
--Bad
EXPLAIN SELECT * FROM (SELECT c1,c2,c3= , sum(c4) FROM limitation_demo GROUP BY c1, = c2, ROLLUP(c3)) AS foo  WHERE c1 = =3D 1 AND c2 =3D 5;
ROLLBACK;


Here are the respective query = plans: 

      =                     =           QUERY PLAN
---------------------------------------------------------------= --------------------
 GroupAggregate =  (cost=3D0.29..8.32 rows=3D1 width=3D20)
  =  Group Key: limitation_demo.c1, limitation_demo.c2, = limitation_demo.c3
   ->  Index Scan = using idx1 on limitation_demo  (cost=3D0.29..8.30 rows=3D1 = width=3D16)
         Index Cond: = ((c1 =3D 1) AND (c2 =3D 5))
(4 rows)

                =                  QUERY = PLAN
---------------------------------------------------------------= -------------
 HashAggregate =  (cost=3D255.02..360.03 rows=3D2 width=3D20)
  =  Hash Key: limitation_demo.c1, limitation_demo.c2, = limitation_demo.c3
   Hash Key: = limitation_demo.c1, limitation_demo.c2
  =  Filter: ((limitation_demo.c1 =3D 1) AND (limitation_demo.c2 =3D = 5))
   ->  Seq Scan on limitation_demo =  (cost=3D0.00..155.01 rows=3D10001 width=3D16)
(5 = rows)

Despite = being semantically equivalent, I believe, the latter plan with the = rollup does not use the index. I believe what is happening is that the = WHERE clause is getting pushed down into the inner query without the = ROLLUP, but is not with the ROLLUP. 

This is a simplified example. In my = real-world use case, the inner query is a view, so I don=E2=80=99t have = the option of moving the where clause. 

Can y=E2=80=99all let me know if I = should submit this somewhere else? Thanks! 

= --Apple-Mail=_B3D63D3E-19FC-4386-B7FF-8B5E118DACEC--