Обсуждение: 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
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
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!
> the strange thing is my btree indexes on the uk roads data work fine.Do they use like, or explicit range-checks?
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