From: Denis @ Next2Me <denis@next2me.com>
To: Martijn van Oosterhout <kleptog@svana.org>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Denis @ Next2Me <denis@next2me.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
Date: Tue, 8 Apr 2003 22:54:46 -0700
Message-ID: <EKEBJNAJDPKJBDFGJNIJGEOADBAA.denis@next2me.com> (raw)
In-Reply-To: <20030409031839.GE15928@svana.org>
Stephan, Martijn,
good call, that was it: the C locale.
I had used all the default settings when installing/creating the database,
and apparently it used my default locale (en_US).
I recreated (initdb) the database with --no-locale, and recreated the database,
and sure enough, the query:
select count(*) from table where table.column like 'fol%'
was a zillion (well almost) time faster than it used to be,
and on pair with mysql's performance.
And as expected, the EXPLAIN on that query does show indeed
the use of the index I had created on the table.
Sweet, I can now nuke mysql out of my system.
Folks, thank you all for the help and other suggestions.
Denis Amselem
Next2Me Inc.
Stephan said:
> If it doesn't use the index (ie, it's still using a sequential scan)
> after the enable_seqscan=off it's likely that you didn't initdb in "C"
> locale in which case like won't use indexes currently (you can see the
> archives for long description, but the short one is that some of the
> locale rules can cause problems with using the index).
Martijn said:
> Ah, but that may be caused by something else altogether. LIKE is only
> indexable in the C locale so if you have en_US as your locale, your LIKE
> won't be indexable. See the discussion threads on this mailing list in the past.
>
>
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-sql@postgresql.org
Cc: denis@next2me.com, kleptog@svana.org, sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
In-Reply-To: <EKEBJNAJDPKJBDFGJNIJGEOADBAA.denis@next2me.com>
* 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