Planner won't use indexes - Mailing list pgsql-general
From | o2@trustcommerce.com |
---|---|
Subject | Planner won't use indexes |
Date | |
Msg-id | 20011010184335.A27482@trustcommerce.com Whole thread Raw |
List | pgsql-general |
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
pgsql-general by date: