* 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
PGP signature
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
