Weird index problem - Mailing list pgsql-general

From Ole Gjerde
Subject Weird index problem
Date
Msg-id Pine.LNX.4.05.9907132215460.24176-100000@snowman.icebox.org
Whole thread Raw
In response to Administration Wizards ...  ("Keith R. Davis" <keidav@whidbey.com>)
Responses More on Weird index problem
List pgsql-general
Hey,
I'm having a very weird thing happening here.
I've had this one table for quite some time, but what I haven't noticed
until now is that only 3 out of 9 indexes seems to be working on it.

OS: Red Hat Linux 6.0 / Linux 2.2.6
Arch: i386
Postgres version: CVS of 6.5 a few days before actual release

Table    = av_parts
+----------------------------------+----------------------------------+-------+
|              Field               |              Type               |Length|
+----------------------------------+----------------------------------+-------+
| itemid                           | int4 not null default nextval (  |4 |
| vendorid                         | int4                             |4 |
| partnumber                       | varchar()                        |25 |
| alternatepartnumber              | varchar()                        |25 |
| nsn                              | varchar()                        |15 |
| description                      | varchar()                        |50 |
| condition                        | varchar()                        |10 |
| quantity                         | int4                             |4 |
| rawpartnumber                    | varchar()                        |25 |
| rawalternatenumber               | varchar()                        |25 |
| rawnsnnumber                     | varchar()                        |15 |
| date                             | int4                             |4 |
| cagecode                         | varchar()                        |10 |
+----------------------------------+----------------------------------+-------+
Indices:  av_parts_altpartnum_index
          av_parts_itemid_key
          av_parts_nsn_index
          av_parts_partnumber_index
          av_parts_rawalternatenumber_ind
          av_parts_rawaltnum_index
          av_parts_rawnsn_index
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
          av_parts_rawpartnumber_index
          av_parts_vendorid_index

This is the one I will use as an example.  This is an index on
rawnsnnumber (varchar 15).

Output of a simple select on that field:
parts=> explain select * from av_parts where rawnsnnumber = '123456';
NOTICE:  QUERY PLAN:

Seq Scan on av_parts  (cost=194841.86 rows=3206928 width=124)

EXPLAIN

This doesn't really make sense.  There is an index on that field, and I
have just done a vacuum on the table.
The index on partnumber, itemid and vendorid is being used properly, all
others are not.
Can someone explain this?

Thanks,
Ole Gjerde


pgsql-general by date:

Previous
From: Chris Walmsley
Date:
Subject: C API comparison..
Next
From: "Erik Colson"
Date:
Subject: Perl - Apache / Postgress