Re: Plan differences

[Anton Melser <melser.anton@xxxxxxxxx>]

I'd bet your old database is in C locale and the new one is not.

Remind me never to never bet against you :-).

 

The LIKE optimization requires an index that's sorted according to plain
C (strcmp) rules.  A regular text index will be that way only if the
database's LC_COLLATE is C.

If you don't want to rebuild the whole database, you can create indexes to
support this by declaring them with COLLATE "C", or the older way is to
declare them with text_pattern_ops as the index opclass.

Declaring new indexes with COLLATE "C" and removing the old indexes fixed the like problem but it created a another - the > and < queries need a sort before passing off the the new index. Having two indexes seems to give me the best of both worlds, though obviously it's taking up (much) more space. As space isn't ever likely to be a problem, and there are no updates (only copy) to these tables, I'll keep it like this to avoid having to reload the entire DB.

Thanks very much for your help.

Cheers,

Anton