Re: [GENERAL] Cant get planner to use index (7.1.3-1PGDG)

> 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.