Re: Plan differences

>
> 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