pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Confusion about locales and 'like' indexes
Date: Tue, 01 Mar 2005 20:42:14 -0500
Message-ID: <3457.1109727734@sss.pgh.pa.us> (raw)
In-Reply-To: <124c40d023eab9ed34fda472fbb3db3f@drivefaster.net>
References: <124c40d023eab9ed34fda472fbb3db3f@drivefaster.net>

Dan Harris <fbsd@drivefaster.net> writes:
> query that uses LIKE.  In my research I have read that the locale 
> setting may affect PostgreSQL's choice of seq scan vs index scan.

Non-C-locale indexes can't support LIKE because the sort ordering
isn't necessarily right.

> I am running Fedora Core 2 and it appears when I run "locale" that it
> is set to 'en.US-UTF-8'.

This is not a definitive indication of the environment the database
sees, though.  Try "show lc_collate".

> I can pg_dump and initdb again with --locale=C if 
> this will allow my LIKE queries to use indexes, but I just wanted to 
> know if there was some other place I needed to change locales in the 
> system? e.g. postgresql.conf or env vars?  Or, would the initdb and 
> reload alone fix it?

That would do it.  Alternatively you can create special-purpose indexes
with one of the xxx_pattern_ops operator classes to support LIKE.

			regards, tom lane



view thread (2+ messages)

Message-ID: <3457.1109727734@sss.pgh.pa.us>
Permalink:  ../3457.1109727734@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3457.1109727734@sss.pgh.pa.us

 · 

reply

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, fbsd@drivefaster.net
  Subject: Re: Confusion about locales and 'like' indexes
  In-Reply-To: <3457.1109727734@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