From: | "William Temperley" <willtemperley(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org(dot) |
Subject: | Ignored btree indexes on particular tables. |
Date: | 2007-12-11 14:25:29 |
Message-ID: | 439dc11e0712110625g78c4a4b8u2d448574cf82c5c6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Harrison | 2007-12-11 14:45:23 | Re: slony question |
Previous Message | David Fetter | 2007-12-11 13:27:25 | Re: comparing rows |