btree index on a char(8) field (fwd)

Hi,

I sent out the forwarded message below last Sunday, and have received no
responses.

Now I realize that this list operates on a volunteer basis and that
everyone is busy, so I am in no way demanding or expecting an answer,
but I would really like to know if there is something wrong with the
message that caused the lack of response.

Does it contain too much or too little detail?  Is it an obvious RTFM
question?  Is this is the correct mailing list?

Basically, I am trying to make use of a btree index on a char(8) field,
the optimizer doesn't want to use it, and I would like to know why.

I was hoping that there might be a simple explanation.  I have searched
the PostgreSQL documentation and the mailing list archives without
success.

I would *greatly* appreciate any response to this message or the
forwarded message, no matter how brief.

Thanks in advance,
../fam

---------- Forwarded message ----------
Date: Sun, 3 Oct 1999 19:50:37 -0400
From: Frank Mandarino <fam@dbsys.risca.com>
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] btree index on a char(8) field

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


************