Re: Re: SQL Where Like - Range it?!

Ashley Clark <aclark(at)ghoti(dot)org> writes:
> db=3D# 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=3D0.00..10.25 rows=3D16 width=3D12)

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

> Seq Scan on builders (cost=3D0.00..9.44 rows=3D355 width=3D12)

> These are the same query, why would the one using index scan have a
> higher cost that the combined condition query?

Always remember that the cost estimates quoted by EXPLAIN are estimates,
not reality.

In this case the reason for the difference is that the planner doesn't
have any detailed understanding of the semantics of bracket-expressions
in regexps, so it doesn't realize that ^[AB] could usefully use an
index. It wants to see ^ followed by at least one character of fixed
pattern before it will think about an indexscan ...

regards, tom lane