Cant get planner to use index (7.1.3-1PGDG) - Mailing list pgsql-general

Something strange is going on.  Postgres keeps wanting to do a sequential
scan of my table when it REALLY should be using it's indexes.

I am running postgresql-7.1.3-1PGDG on RedHat 6.2 and on RedHat 7.0.

I have 300,000 records in this table and yes, I have vacuum analyzed.

Here is my table:

----------------------------------------------------
fdb=> \d mfps_orderinfo_435
        Table "mfps_orderinfo_435"
      Attribute      |  Type   | Modifier
---------------------+---------+----------
 order_number        | integer | not null
 source_code         | text    |
 last_name           | text    |
 first_name          | text    |
 title               | text    |
 address1            | text    |
 address2            | text    |
 city                | text    |
 state               | text    |
 zip                 | text    |
 telephone           | text    |
 bill_method         | text    |
 cc                  | text    |
 exp                 | text    |
 cc_auth_code        | text    |
 multi_billing_code  | text    |
 order_header_status | text    |
 order_date          | date    |
 ship_date           | date    |
 total_quantity      | integer |
 order_extension     | money   |
 sales_tax           | money   |
 shipping            | money   |
 total_discount      | money   |
 return_quantity     | integer |
 return_amount       | money   |
 num_billings        | integer |
 tracking_no1        | text    |
 tracking_no2        | text    |
 tracking_no3        | text    |
 email               | text    |
 amount_paid         | money   |
Indices: idx_mfps_orderinfo_435_odate,
         idx_mfps_orderinfo_435_fname,
         idx_mfps_orderinfo_435_lname,
         mfps_orderinfo_435_pkey
----------------------------------------------------

And here are two relevant indexes:

----------------------------------------------------
fdb=> \d idx_mfps_orderinfo_435_odate
Index "idx_mfps_orderinfo_435_odate"
 Attribute  | Type
------------+------
 order_date | date
btree

fdb=> \d idx_mfps_orderinfo_435_fname
Index "idx_mfps_orderinfo_435_fname"
 Attribute  | Type
------------+------
 first_name | text
btree
----------------------------------------------------

Now, Here's where things get weird.

----------------------------------------------------
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date =
current_date;
NOTICE:  QUERY PLAN:

Aggregate  (cost=13532.12..13532.12 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..13528.77 rows=1340
width=0)

EXPLAIN
----------------------------------------------------

Here it does a straight date compare and it chooses not to use the index.
What??

----------------------------------------------------
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name =
'SMITH';
NOTICE:  QUERY PLAN:

Aggregate  (cost=1044.16..1044.16 rows=1 width=0)
  ->  Index Scan using idx_mfps_orderinfo_435_fname on mfps_orderinfo_435
(cost=0.00..1043.47 rows=279 width=0)

EXPLAIN
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name
like 'SMITH%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=12769.48..12769.48 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=1 width=0)

EXPLAIN
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name
like 'SMITH';
NOTICE:  QUERY PLAN:

Aggregate  (cost=12770.17..12770.17 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=279 width=0)

EXPLAIN
----------------------------------------------------

Here it will do an index scan if and only if I use the '=' operator.  If I
use like with the % at the end of the string or EVEN if I have no wild card
at all... it still does a seq scan.   If anyone has any advice on how to
get these indexes working properly, please let me know.

        Orion Henry