Makes sense - but I'm using the PG substring function here.
LEFT OUTER JOIN main.ship_event se ON (se.ship_event_nm = substring(dp.shipevent from 6 for 5))
In this case, the dp.shipevent is in the fdw-accessed MySql table. Shouldn't that force the sort to be local?
-----Original Message-----
From: Christophe Pettus <xof@thebuild.com>
Sent: Monday, January 30, 2023 1:20 PM
To: Pete Storer <Pete.Storer@sas.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Joe Conway <mail@joeconway.com>; Bruce Momjian <bruce@momjian.us>;
pgsql-bugs@lists.postgresql.org
Subject: Re: Query execution failure
[You don't often get email from xof@thebuild.com. Learn why this is important at
https://aka.ms/LearnAboutSenderIdentification]
EXTERNAL
> On Jan 30, 2023, at 10:17, Pete Storer <Pete.Storer@sas.com> wrote:
>
> This is very interesting, guys. And it leads me to a question: Is there any way to FORCE Postgres to do a local
sort/collationof any data that is accessed through a FDW?
It depends on the FDW. One thing to try is to have the SORT be an expression, especially one that depends on functions
thatcan't otherwise be pushed down. (You may have to do some experimenting to find one that returns identity, i.e. the
samesort order as just the bare column, but isn't pushed down.)