btree index on a char(8) field

From: Frank Mandarino <fam(at)risca(dot)com>
To: pgsql-general(at)postgreSQL(dot)org
Subject: btree index on a char(8) field
Date: 1999-10-03 23:50:37
Message-ID: 99Oct3.195125edt.115201@sky.risca.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)risca(dot)com

Browse pgsql-general by date

  From Date Subject
Next Message The Hermit Hacker 1999-10-04 00:20:32 Re: [GENERAL] 6.5.2 patch
Previous Message Doran L. Barton 1999-10-03 23:20:09 Re: [GENERAL] problem compiling pgsql 6.5.1 on SuSE 6.2