MIME-Version: 1.0
Date: Wed, 22 Nov 2023 09:32:58 +0300
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>,
 "Fujii.Yuki@df.MitsubishiElectric.co.jp"
 <Fujii.Yuki@df.mitsubishielectric.co.jp>, PostgreSQL-development
 <pgsql-hackers@postgresql.org>, "Finnerty, Jim" <jfinnert@amazon.com>,
 Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Tomas
 Vondra <tomas.vondra@enterprisedb.com>, Julien Rouhaud <rjuju123@gmail.com>,
 Daniel Gustafsson <daniel@yesql.se>
Subject: Re: Partial aggregates pushdown
In-Reply-To: <CA+TgmoaiH_Na3y17PankZH59EgJWbgdPM-szb1eP6DQoOAMgYg@mail.gmail.com>
References: <TYTPR01MB1095486F5E3103D783AF4748995DFA@TYTPR01MB10954.jpnprd01.prod.outlook.com>
 <ZTmR1wIr2_A-vw33@momjian.us>
 <TYTPR01MB10954D890D8F729E1BCE42D4F95DDA@TYTPR01MB10954.jpnprd01.prod.outlook.com>
 <ZTrBYS71CrnnA7xa@momjian.us>
 <TYTPR01MB10954B4C9A791FA9D2BF692F095DCA@TYTPR01MB10954.jpnprd01.prod.outlook.com>
 <ZTspKdU1sZ4qKLD8@momjian.us>
 <TYTPR01MB10954B96601911FE868BF88E895DCA@TYTPR01MB10954.jpnprd01.prod.outlook.com>
 <OSZPR01MB9424BB7E885791D88B80F7B395B3A@OSZPR01MB9424.jpnprd01.prod.outlook.com>
 <OSZPR01MB94244C52ABBE67C4A033B5E495B3A@OSZPR01MB9424.jpnprd01.prod.outlook.com>
 <CA+Tgmobvja+jytj5zcEcYgqzOaeJiqrrJxgqDf1q=3k8FepuWQ@mail.gmail.com>
 <ZVviSttXc7kff_I5@momjian.us>
 <CA+TgmoaiH_Na3y17PankZH59EgJWbgdPM-szb1eP6DQoOAMgYg@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <8175ddeb6d417d8a1f91e667fef77abf@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8175ddeb6d417d8a1f91e667fef77abf%40postgrespro.ru>
Precedence: bulk

Robert Haas писал 2023-11-21 20:16:

>> > I don't think the patch does a good job explaining why HAVING,
>> > DISTINCT, and ORDER BY are a problem. It seems to me that HAVING
>> > shouldn't really be a problem, because HAVING is basically a WHERE
>> > clause that occurs after aggregation is complete, and whether or not
>> > the aggregation is safe shouldn't depend on what we're going to do
>> > with the value afterward. The HAVING clause can't necessarily be
>> > pushed to the remote side, but I don't see how or why it could make
>> > the aggregate itself unsafe to push down. DISTINCT and ORDER BY are a
>> > little trickier: if we pushed down DISTINCT, we'd still have to
>> > re-DISTINCT-ify when combining locally, and if we pushed down ORDER
>> > BY, we'd have to do a merge pass to combine the returned values unless
>> > we could prove that the partitions were non-overlapping ranges that
>> > would be visited in the correct order. Although that all sounds
>> > doable, I think it's probably a good thing that the current patch
>> > doesn't try to handle it -- this is complicated already. But it should
>> > explain why it's not handling it and maybe even a bit about how it
>> > could be handling in the future, rather than just saying "well, this
>> > kind of thing is not safe." The trouble with that explanation is that
>> > it does nothing to help the reader understand whether the thing in
>> > question is *fundamentally* unsafe or whether we just don't have the
>> > right code to make it work.
>> 
>> Makes sense.
> 
> Actually, I think I was wrong about this. We can't handle ORDER BY or
> DISTINCT because we can't distinct-ify or order after we've already
> partially aggregated. At least not in general, and not without
> additional aggregate support functions. So what I said above was wrong
> with respect to those. Or so I believe, anyway. But I still don't see
> why HAVING should be a problem.

Hi. HAVING is also a problem. Consider the following query

SELECT count(a) FROM t HAVING count(a) > 10 - we can't push it down to
foreign server as HAVING needs full aggregate result, but foreign server
don't know it.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional