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
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 |