Re: SQL Where Like - Range it?!

* will trillich in "Re: Re: SQL Where Like - Range it?!" dated
* 2001/04/28 03:17 wrote:

> apparently it does IF you use the 'anchor-at-beginning'
> construct, namely the "^":
>
>     fld ~ '^[A-F]' -- STARTS with A,B,C,D,E, or F
>     fld ~ '[A-F]'  -- merely contains A,B,C,D,E, or F
>     fld ~ '[A-F]$' -- ENDS with A-F
>
> if starts-with (^) then it uses the index. so i hear.

Being the curious sort that I am I tried a few things and got some more
questions.

db=# explain SELECT name from builders where name ~ '^A' or name ~ '^B';
NOTICE:  QUERY PLAN:

Index Scan using builders_name_key, builders_name_key on builders
(cost=0.00..10.25 rows=16 width=12)

EXPLAIN
db=# explain SELECT name from builders where name ~ '^[AB]';
NOTICE:  QUERY PLAN:

Seq Scan on builders  (cost=0.00..9.44 rows=355 width=12)

EXPLAIN

These are the same query, why would the one using index scan have a
higher cost that the combined condition query? Shouldn't they be the
same? And which one is faster/scales better?

And one more question

db=# explain SELECT name from builders where name like 'A%' or name
     like 'B%';
NOTICE:  QUERY PLAN:

Index Scan using builders_name_key, builders_name_key on builders
(cost=0.00..10.25 rows=16 width=12)

EXPLAIN

Does the similarity of these numbers to the first ones above have any
significance or is it just coincidence?

--
ashley clark