Pete Storer <Pete.Storer@sas.com> writes:
> When trying to run a complex join of eight tables - including 6 foreign tables - the query runs for a few seconds and
thenterminates with an error message
> ERROR: mergejoin input data is out of order
You have not shown us the table definitions, nor an EXPLAIN for the
query, but this'd indicate that the sort ordering of one or more
tables is not like that of the others. Likely causes of this include
* corrupt index, if an indexscan is being used instead of an explicit
sort
* foreign server has a different idea of the locale's sort details than
the local server does (or than some other foreign server, if the
query touches more than one)
If the join key columns are all numeric then it's almost surely a corrupt
index somewhere. But if you are joining on string keys then you are
subject to the whims of the locale's sort order, and that is a mess,
particularly for non-ASCII data. Not only might it be different on
different platforms, but even on the same platform it can change over
time (with the effect of making indexes on string columns corrupt).
There's some details and advice here:
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane