From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anton Melser <melser.anton@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Plan differences
In-reply-to: 
 <CAKywjPoJS4hQiLTukMDUCvmaA_aJp-W11tvnu-=wnwaL0vuqQA@mail.gmail.com>
References: 
 <CAKywjPoJS4hQiLTukMDUCvmaA_aJp-W11tvnu-=wnwaL0vuqQA@mail.gmail.com>
Comments: In-reply-to Anton Melser <melser.anton@gmail.com>
 message dated "Thu, 31 Dec 2015 11:12:58 +0100"
Date: Thu, 31 Dec 2015 10:02:02 -0500
Message-ID: <10594.1451574122@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Anton Melser <melser.anton@gmail.com> writes:
> I moved a DB between two "somewhat" similar Postgres installs and am
> getting much worse plans on the second. The DB was dumped via pg_dump
> (keeping indexes, etc.) and loaded to the new server.

> [ "like 'foo%'" is not getting converted into index bounds ]

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

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.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance