From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | William Temperley <willtemperley(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Ignored btree indexes on particular tables. |
Date: | 2007-12-11 14:51:33 |
Message-ID: | 475EA3F5.1040203@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
William Temperley wrote:
> My problem is I have several text fields in the address data, for which
> postgres ignores the indexes (btree).
> "Index Scan using ap_idx_pc on ap (cost= 0.00..15.30 rows=1 width=188)"
> " Index Cond: (((pc_)::text >= 'OX2 0'::character varying) AND ((pc_)::text
> < 'OX2 1'::character varying))"
> " Filter: ((pc_)::text ~~ 'OX2 0%'::text)"
>
> And the NEW:-
> "Seq Scan on ap (cost=0.00..4652339.33 rows=1 width=189)"
> " Filter: ((pc_)::text ~~ 'OX2 0%'::text)"
It's almost certainly a locale thing. Your old locale was "C" and the
new one is "en_GB.UTF-8" or similar. This means that simple sorting has
been replaced by something more library-like.
You can either dump the database, re-run initdb with the "C" locale and
restore, or read up on text_pattern_ops/varchar_pattern_ops in the
manual (11.8. Operator Classes). Basically it tags an index as working
with pattern-matching in the current locale.
> the strange thing is my btree indexes on the uk roads data work fine.
Do they use like, or explicit range-checks?
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-12-11 14:57:57 | Re: Hijack! |
Previous Message | Keith Turner | 2007-12-11 14:48:35 | Hijack! |