> On Mar 5, 2019, at 11:55 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Matthew Pounsett <matt@xxxxxxxxxxxxx> writes: >> On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@xxxxxxxxxxxxx> 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 like a champ, and we seem to be back in business. Casey
