Re: Is this expected? (Index scanning)

On Sun, 6 May 2001, Mitch Vincent wrote:

> Indexes on both void and lower(cust_name) here..
>
> I get ->
>
> EXPLAIN select * from invoice_master WHERE lower(cust_name) like 'mitch' AND
> void = 'f' order by created LIMIT 100;
> NOTICE: QUERY PLAN:
>
> Limit (cost=2241.98..2241.98 rows=100 width=319)
> -> Sort (cost=2241.98..2241.98 rows=604 width=319)
> -> Index Scan using invoice_cust_name on invoice_master
> (cost=0.00..2214.09 rows=604 width=319)
>
> The above query matches zero rows.
>
> EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like '%mitch'
> AND void = 'f' ORDER BY created LIMIT 100;
> NOTICE: QUERY PLAN:
>
> Limit (cost=4310.65..4310.65 rows=1 width=319)
> -> Sort (cost=4310.65..4310.65 rows=1 width=319)
> -> Seq Scan on invoice_master (cost=0.00..4310.64 rows=1
> width=319)
>
> The above query matches zero rows.
>
> EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like 'mitch%'
> AND void = 'f' ORDER BY created LIMIT 100;
> NOTICE: QUERY PLAN:
>
> Limit (cost=2241.98..2241.98 rows=100 width=319)
> -> Sort (cost=2241.98..2241.98 rows=604 width=319)
> -> Index Scan using invoice_cust_name on invoice_master
> (cost=0.00..2214.09 rows=604 width=319)
>
> The above query matches 53 rows..

In the first and last case the like is anchored at the beginning so it
uses the name index. The middle case can't use that index, and how
well distributed are the values of void (is it a boolean?)? It's likely
that the index isn't very selective, if most of the rows are void='f'
then that index is probably more expensive to scan than the sequence scan
would be.