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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs 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

Responses

Browse pgsql-bugs by date

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

Browse pgsql-general by date

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