Обсуждение: Ignored btree indexes on particular tables.

Hi all

I've recently installed pg 8.2.5 on a new server and transferred my data from 8.2.4 running on a slow old thing, via pg_dump.

One of these tables has point UK address data, with 27 million rows, and another the UK roads data, approx 4 million rows.

My problem is I have several text fields in the address data, for which postgres ignores the indexes (btree).

Using my pc_ (postcode) column:
Here's the OLD query plan on the old server.

"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)"

I have tried :
reindexing.
dropping the index and recreating it.
set enable_seqscan = off;
set seq_page_cost = 1000;
vacuum analyze;
vacuum full;

none of these things have worked.

the strange thing is my btree indexes on the uk roads data work fine.

There are quite a few nulls in the table, but very few in the pc column I've been using as an example.

Any help would be greatly appreciated.

Cheers

Will

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

Genius!

Thanks Richard,

The old locale was C and the new one English_United Kingdom.1252

I created a new index with "varchar_pattern_ops" and off it went!

I wasn't using like, which is why they were working. adding a new index then using the like operator didn't work.

Thanks again.

Will

William Temperley wrote:
> Genius!
>
> Thanks Richard,
>
> The old locale was C and the new one English_United Kingdom.1252
> I created a new index with "varchar_pattern_ops" and off it went!

Catches everyone first time.

--
   Richard Huxton
   Archonet Ltd