> 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) > > Here it does a straight date compare and it chooses not to use the index. > What??
It's probably deciding that the number of rows (1340) is large enough that the index scan isn't a win. Short form is that due to the way things are structured, after a certain point the index scan becomes more expensive than sequentially scanning the table. > 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. You may want to check your locale setting. IIRC, if you're running with locale enabled and not in C locale, LIKE does not get optimized to run with indexes. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
