Re: SQL Where Like - Range it?! - Mailing list pgsql-general

From Ashley Clark
Subject Re: SQL Where Like - Range it?!
Date
Msg-id 20010428151420.A2777@ghoti.org
Whole thread Raw
In response to Re: Re: SQL Where Like - Range it?!  (will trillich <will@serensoft.com>)
Responses Re: Re: SQL Where Like - Range it?!
List pgsql-general
* 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

Attachment

pgsql-general by date:

Previous
From: Sterling
Date:
Subject: Re: SQL Where Like - Range it?!
Next
From: Paul Tomblin
Date:
Subject: Re: Why is this taking so damn long?