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
