MIME-Version: 1.0
Date: Tue, 28 May 2024 08:45:04 +0300
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: "Fujii.Yuki@df.MitsubishiElectric.co.jp"
 <Fujii.Yuki@df.mitsubishielectric.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>, PostgreSQL-development
 <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian
 <bruce@momjian.us>, Stephen Frost <sfrost@snowman.net>, Andres Freund
 <andres@anarazel.de>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Julien
 Rouhaud <rjuju123@gmail.com>, Daniel Gustafsson <daniel@yesql.se>, vignesh C
 <vignesh21@gmail.com>
Subject: Re: Partial aggregates pushdown
In-Reply-To: <TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com>
References: <OSZPR01MB94244C52ABBE67C4A033B5E495B3A@OSZPR01MB9424.jpnprd01.prod.outlook.com>
 <CA+Tgmobvja+jytj5zcEcYgqzOaeJiqrrJxgqDf1q=3k8FepuWQ@mail.gmail.com>
 <ZVviSttXc7kff_I5@momjian.us>
 <CA+TgmoaiH_Na3y17PankZH59EgJWbgdPM-szb1eP6DQoOAMgYg@mail.gmail.com>
 <8175ddeb6d417d8a1f91e667fef77abf@postgrespro.ru>
 <TYAPR01MB55149B8569F2FDA262654C4F95BAA@TYAPR01MB5514.jpnprd01.prod.outlook.com>
 <CA+TgmoYQKF-8vL8sctWuw_dskBiQWcspuNPJuH4ttusuiP529g@mail.gmail.com>
 <CA+TgmoZHtdFEOULVXqhWgSg36R1e1ZEdSKJKb+VzTfRU0HaSZQ@mail.gmail.com>
 <4012625.1701120204@sss.pgh.pa.us>
 <CA+Tgmob7Dn_+UN1qgb_NUEoeg1jdug5mh+qz7q3Ed1NHAL_LNQ@mail.gmail.com>
 <ZWX4H9EDA7fHiIaU@tamriel.snowman.net>
 <TYAPR01MB551411A7DDFF0B38C5AAEDFE9584A@TYAPR01MB5514.jpnprd01.prod.outlook.com>
 <CA+TgmoYCrtOvk2f32qQKZV=jNL35tandf2A2Dp_2F5ASuiG1BA@mail.gmail.com>
 <TYAPR01MB5514C939F39F97BC171F58FD958BA@TYAPR01MB5514.jpnprd01.prod.outlook.com>
 <CALDaNm0S5fb_8o+TtR8bXf3zOpqabOBLy0EJ3+oVzHH_dzpXvw@mail.gmail.com>
 <TYAPR01MB5514F0CBD9CD4F84A261198195562@TYAPR01MB5514.jpnprd01.prod.outlook.com>
 <bf816db00fb5eda86358e10a18a330e5@postgrespro.ru>
 <TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.jpnprd01.prod.outlook.com>
 <31ab8f7b91cef6e837b3e31ea1e35a9a@postgrespro.ru>
 <b6205c8ccce1b7bf7c750d6f01c665d4@postgrespro.ru>
 <f35243f029bf292e297b16774b4d5116@postgrespro.ru>
 <TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com>
User-Agent: Roundcube Webmail/1.6.5
Message-ID: <d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d15cb3cb52ffa5f49cf78596eecba04d%40postgrespro.ru>
Precedence: bulk

Fujii.Yuki@df.MitsubishiElectric.co.jp писал(а) 2024-05-28 00:30:
> Hi Mr. Pyhalov.
> 
Hi.

>> Found one more problem. You can fire partial aggregate over 
>> partitioned
>> table, but convert_combining_aggrefs() will make non-partial copy, 
>> which
>> leads to
>> 'variable not found in subplan target list' error.
> Thanks for the correction as well.
> As you pointed out,
> the original patch certainly had the potential to cause problems.
> However, I could not actually reproduce the problem in cases such as 
> the following.
> 
>   Settings:
>     t(c1, c2) is a patitioned table whose partition key is c1.
>     t1, t2 are patitions of t and are partitioned table.
>     t11, t12: partitions of t1 and foreign table of postgres_fdw.
>     t21, t22: partitions of t2 and foreign table of postgres_fdw.
>   Query:
>     select c2 / 2, sum(c1) from t group by c2 / 2 order by 1
> 
> If you have a reproducible example, I would like to add it to
> the regression test.
> Do you have a reproducible example?
> 

The fix was to set child_agg->agg_partial to orig_agg->agg_partial in 
convert_combining_aggrefs(), it's already in the patch,
as well as the example - without this fix

-- Check partial aggregate over partitioned table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;

fails with

ERROR:  variable not found in subplan target list
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional