Skip site navigation (1) Skip section navigation (2)

Cant get planner to use index (7.1.3-1PGDG)

From: Orion <orion(at)dusk(dot)org>
To: pgsql-bugs(at)postgresql(dot)org(dot)pgsql-general(at)postgresql(dot)org
Subject: Cant get planner to use index (7.1.3-1PGDG)
Date: 2001-10-11 19:39:38
Message-ID: 9q4s9s$2msv$1@news.tht.net (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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





Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-10-11 20:25:27
Subject: Re: SQLCODE==-209
Previous:From: Bruce MomjianDate: 2001-10-11 16:49:57
Subject: Re: Bug #463: Make 'infinity' work with type 'date'

pgsql-general by date

Next:From: Peter EisentrautDate: 2001-10-11 20:03:47
Subject: Re: "Relation x does not exist" error when x does exist
Previous:From: Allan EngelhardtDate: 2001-10-11 19:17:28
Subject: Re: error codes when running pg_dumpall from a perl script.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group