> On Mar 5, 2019, at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Matthew Pounsett <matt@conundrum.com> writes:
>> On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Given that (a) this was triggered by a server migration and (b)
>>> the leading column of the index looks like it's probably varchar,
>>> I'm suspicious that the new server has different collation behavior.
>
>> The migration in question was an rsync from a Debian 9 box
>> running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20.
>
> Yeah, that would fit the theory :-(. Debian would be using glibc
> and FreeBSD would not be. If you were using C collation in the
> database, you'd be all right because that's standardized, but I'll
> bet you were using something else. What does psql \l show for the
> "collate" setting of this database? (Or, if by chance you had an
> explicit COLLATE setting on the column in question, what's that?)
>
> In any case, you should be reindexing any indexes on textual columns
> that were not using "C" collation; none of them can be trusted.
> The system catalogs should be OK.
Many thanks to you and everyone who helped with this issue, with informative and actionable advice. Reindexing worked
likea champ, and we seem to be back in business.
Casey