Something strange is going on. Postgres keeps wanting to do a sequential scan of my table when it REALLY should be
usingit'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
orEVEN if I have no wild card at all... it still does a seq scan. If anyone has any advice on how to get these
indexesworking properly, please let me know.
Orion Henry
