I am in the process of migrating a database from Postgres95 2.0 to
PostgreSQL 6.5.2 on a Debian 2.1 system.
In a few of the tables, a char8 type field was used for the primary key,
so I converted them to type char(8). The tables also had a btree index
built on the primary key using char8_ops, which I converted to char_ops.
Now I am finding that explain is indicating that the index is never
used, even for queries that I would have thought would run faster using
an index.
For example:
main=> \d vendor
Table = vendor
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| ven_code | char() | 8 |
| initials | char() | 2 |
| tax_number | text | var |
| check_payable_to | text | var |
| other_name | text | var |
| address_1 | text | var |
| address_2 | text | var |
| city | text | var |
| ps | text | var |
| country | text | var |
| postal_code | text | var |
| work_phone | text | var |
| home_phone | text | var |
| fax_phone | text | var |
| bank_code | text | var |
| trans_num | text | var |
| bank_acc_num | text | var |
| payment_restriction_flag | char() | 1 |
| debt_reason_msg | text | var |
| debt_caution_msg | text | var |
| comments_1 | text | var |
| comments_2 | text | var |
| special_order | char() | 1 |
| status | char() | 1 |
+----------------------------------+----------------------------------+-------+
main=> select count(*) from vendor;
count
-----
9905
(1 row)
main=> create index ven_code_idx on vendor using btree (ven_code char_ops);
CREATE
main=> vacuum analyze;
VACUUM
main=> explain select ven_code,initials,city from vendor where ven_code='P8979';
NOTICE: QUERY PLAN:
Seq Scan on vendor (cost=738.86 rows=2 width=36)
EXPLAIN
main=>
Under Postgres95, the index was used from such queries. Can anyone tell
me why the index isn't being used in PostgreSQL?
Thanks,
../fam
--
Frank A. Mandarino
fam@risca.com