pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexes with LIKE
4+ messages / 3 participants
[nested] [flat]
* Indexes with LIKE
@ 2002-07-13 09:53 Julian Scarfe <julian.scarfe@ntlworld.com>
2002-07-13 15:28 ` Re: Indexes with LIKE Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Julian Scarfe @ 2002-07-13 09:53 UTC (permalink / raw)
To: pgsql-sql
I've noticed a difference in behaviour between a server running version 6.x
and one running 7.2.1 in use of indexes with LIKE.
With an index on foo,
WHERE foo LIKE 'bar%'
uses the index (as I would expect it to) on the 6.x box, but uses a
sequential scan (really slow) on the 7.2.1 box.
It's possible that I've set (or failed to set) a run-time parameter that
controls the behaviour, but I don't know what that might be. ANALYZEing the
table doesn't modify the behaviour.
I can't find any mention of this in the documentation -- any pointers would
be appreciated.
If the above doesn't ring any bells, I'll put together an example.
Many thanks
Julian Scarfe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexes with LIKE
2002-07-13 09:53 Indexes with LIKE Julian Scarfe <julian.scarfe@ntlworld.com>
@ 2002-07-13 15:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-13 18:18 ` Re: Indexes with LIKE Julian Scarfe <julian@avbrief.com>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-07-13 15:28 UTC (permalink / raw)
To: Julian Scarfe <julian.scarfe@ntlworld.com>; +Cc: pgsql-sql
On Sat, 13 Jul 2002, Julian Scarfe wrote:
> I've noticed a difference in behaviour between a server running version 6.x
> and one running 7.2.1 in use of indexes with LIKE.
>
> With an index on foo,
>
> WHERE foo LIKE 'bar%'
>
> uses the index (as I would expect it to) on the 6.x box, but uses a
> sequential scan (really slow) on the 7.2.1 box.
> It's possible that I've set (or failed to set) a run-time parameter that
> controls the behaviour, but I don't know what that might be. ANALYZEing the
> table doesn't modify the behaviour.
>
> I can't find any mention of this in the documentation -- any pointers would
> be appreciated.
You need to have made the database in C locale in order to get index scans
from LIKE. I think that's mentioned in the Localization section of the
admin guide, but I could be remembering that wrong.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexes with LIKE
2002-07-13 09:53 Indexes with LIKE Julian Scarfe <julian.scarfe@ntlworld.com>
2002-07-13 15:28 ` Re: Indexes with LIKE Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-07-13 18:18 ` Julian Scarfe <julian@avbrief.com>
2002-07-14 19:46 ` Re: Indexes with LIKE Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Julian Scarfe @ 2002-07-13 18:18 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
> You need to have made the database in C locale in order to get index scans
> from LIKE. I think that's mentioned in the Localization section of the
> admin guide, but I could be remembering that wrong.
Thanks very much Stephan. Indeed it's in Admin Guide 5.1.2. I was using
en_GB, not C.
There is of course no excuse for failing to read every bit of smallprint in
the admin guide before installation :-) but for those lazy unfortunates like
me that don't think to look there to find out what's wrong when indexes
aren't used with LIKE, I've added a DocNote under Pattern Matching.
Julian Scarfe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Indexes with LIKE
2002-07-13 09:53 Indexes with LIKE Julian Scarfe <julian.scarfe@ntlworld.com>
2002-07-13 15:28 ` Re: Indexes with LIKE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-13 18:18 ` Re: Indexes with LIKE Julian Scarfe <julian@avbrief.com>
@ 2002-07-14 19:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-07-14 19:46 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
On Sat, 13 Jul 2002, Julian Scarfe wrote:
> From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
>
> > You need to have made the database in C locale in order to get index scans
> > from LIKE. I think that's mentioned in the Localization section of the
> > admin guide, but I could be remembering that wrong.
>
> Thanks very much Stephan. Indeed it's in Admin Guide 5.1.2. I was using
> en_GB, not C.
>
> There is of course no excuse for failing to read every bit of smallprint in
> the admin guide before installation :-) but for those lazy unfortunates like
> me that don't think to look there to find out what's wrong when indexes
> aren't used with LIKE, I've added a DocNote under Pattern Matching.
That's a good idea. Alot of the docs are written assuming that you're
configuring it from source so that you'd have to turn on locale at
configure time, but that's not true for package users.
Are you actually using any of the locale features of the locale? If not,
you might want to consider setting up in C locale.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-14 19:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-13 09:53 Indexes with LIKE Julian Scarfe <julian.scarfe@ntlworld.com>
2002-07-13 15:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-13 18:18 ` Julian Scarfe <julian@avbrief.com>
2002-07-14 19:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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