From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andy Fan <zhihui.fan1213@gmail.com>
cc: Andrew Gierth <andrew@tao11.riddles.org.uk>,
        David Rowley <dgrowleyml@gmail.com>, pavelsivash@gmail.com,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16585: Wrong filtering on a COALESCE field after using
 GROUPING SETS
In-reply-to: 
 <CAKU4AWoE+QpHbV_tEyGaz_uxvffJnbHuFRK2fXmfs_bj2fKWww@mail.gmail.com>
References: <16585-9d8c340d23ade8c1@postgresql.org>
 <CAApHDvoP02UYOS5H82GfDvF_3X9CvWdbseqq-rjufE3GEYRPPA@mail.gmail.com>
 <87mu2qixx5.fsf@news-spur.riddles.org.uk> <1750212.1597958727@sss.pgh.pa.us>
 <87tuwxrnzm.fsf@news-spur.riddles.org.uk>
 <CAKU4AWoE+QpHbV_tEyGaz_uxvffJnbHuFRK2fXmfs_bj2fKWww@mail.gmail.com>
Comments: In-reply-to Andy Fan <zhihui.fan1213@gmail.com>
	message dated "Fri, 21 Aug 2020 10:08:42 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1765055.1597977852.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 20 Aug 2020 22:44:12 -0400
Message-ID: <1765056.1597977852@sss.pgh.pa.us>
Precedence: bulk

Andy Fan <zhihui.fan1213@gmail.com> writes:
> On Fri, Aug 21, 2020 at 5:51 AM Andrew Gierth <andrew@tao11.riddles.org.=
uk>
> wrote:
>> Unless I'm missing something, it should be safe to reference output
>> columns that are not mentioned in any grouping set,

> I think such columns usually are aggregation expr,  If we want to push d=
own
> a qual which reference to an aggregation expr,  we have to push down
> to having cause, However I am not sure such pushing down really helps.

Well, they can either be aggregates, or functions of the grouping
columns.  You're right that there's not much we can do (today) with
restrictions on aggregate outputs, but there can be value in pushing
down restrictions on the other sort.

As an example, consider the regression database's tenk1 table, and
for argument's sake add

regression=3D# create index on tenk1 (abs(hundred));
CREATE INDEX

Then we can get

regression=3D# explain select * from (select hundred, ten, abs(hundred) a,=
 count(*) c from tenk1 group by 1,2) ss where a =3D 42;
                                     QUERY PLAN                           =
          =

--------------------------------------------------------------------------=
----------
 HashAggregate  (cost=3D225.98..227.18 rows=3D96 width=3D20)
   Group Key: tenk1.hundred, tenk1.ten
   ->  Bitmap Heap Scan on tenk1  (cost=3D5.06..225.23 rows=3D100 width=3D=
8)
         Recheck Cond: (abs(hundred) =3D 42)
         ->  Bitmap Index Scan on tenk1_abs_idx  (cost=3D0.00..5.04 rows=3D=
100 width=3D0)
               Index Cond: (abs(hundred) =3D 42)
(6 rows)

which is a lot cheaper than the pure seqscan you get with no pushed-down
condition.

One thing that I find curious is that if I alter this example to use
grouping sets, say

regression=3D# explain select * from (select hundred, ten, abs(hundred) a,=
 count(*) c from tenk1 group by grouping sets (1,2)) ss where a =3D 42;
                           QUERY PLAN                            =

-----------------------------------------------------------------
 HashAggregate  (cost=3D495.00..546.65 rows=3D2 width=3D20)
   Hash Key: tenk1.hundred
   Hash Key: tenk1.ten
   Filter: (abs(tenk1.hundred) =3D 42)
   ->  Seq Scan on tenk1  (cost=3D0.00..445.00 rows=3D10000 width=3D8)
(5 rows)

i.e. it's not seeing the abs() condition as pushable below the
aggregation.  I'm not quite sure if that's a necessary restriction
or a missed optimization.

			regards, tom lane