pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Confusion about locales and 'like' indexes
2+ messages / 2 participants
[nested] [flat]

* Confusion about locales and 'like' indexes
@ 2005-03-02 00:44 Dan Harris <fbsd@drivefaster.net>
  2005-03-02 01:42 ` Re: Confusion about locales and 'like' indexes Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Dan Harris @ 2005-03-02 00:44 UTC (permalink / raw)
  To: pgsql-performance

Greetings,

I have been beating myself up today trying to optimize indices for a 
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.  I am 
running Fedora Core 2 and it appears when I run "locale" that it is set 
to 'en.US-UTF-8'.

Did I fall into a "gotcha" trap here about C vs non-C locales?  I'm not 
much of a C programmer so I have no idea what all this touches and 
everything has been left as default during PG compilation as well as 
Fedora install.   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?

I'm running 8.0.1 if that matters.

Thanks




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Confusion about locales and 'like' indexes
  2005-03-02 00:44 Confusion about locales and 'like' indexes Dan Harris <fbsd@drivefaster.net>
@ 2005-03-02 01:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-03-02 01:42 UTC (permalink / raw)
  To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-02 01:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-02 00:44 Confusion about locales and 'like' indexes Dan Harris <fbsd@drivefaster.net>
2005-03-02 01:42 ` Tom Lane <tgl@sss.pgh.pa.us>

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