From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anton Melser <melser.anton@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Plan differences
Date: Thu, 31 Dec 2015 10:02:02 -0500
Message-ID: <10594.1451574122@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKywjPoJS4hQiLTukMDUCvmaA_aJp-W11tvnu-=wnwaL0vuqQA@mail.gmail.com>
References: <CAKywjPoJS4hQiLTukMDUCvmaA_aJp-W11tvnu-=wnwaL0vuqQA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, melser.anton@gmail.com
Subject: Re: Plan differences
In-Reply-To: <10594.1451574122@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox