pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed 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>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: vignesh C <vignesh21@gmail.com>
Subject: Re: Partial aggregates pushdown
Date: Tue, 28 May 2024 08:45:04 +0300
Message-ID: <d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru> (raw )
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 >
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
view thread (198+ messages) latest in thread
Message-ID: <d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru>
Permalink: ../d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru/
Also on: postgresql.org/message-id/d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.pyhalov@postgrespro.ru, Fujii.Yuki@df.mitsubishielectric.co.jp, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, bruce@momjian.us, sfrost@snowman.net, andres@anarazel.de, tomas.vondra@enterprisedb.com, rjuju123@gmail.com, daniel@yesql.se, vignesh21@gmail.com
Subject: Re: Partial aggregates pushdown
In-Reply-To: <d15cb3cb52ffa5f49cf78596eecba04d@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox