'like' refuses to use an index???

From: Dima Tkach <dmitry(at)openratings(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: 'like' refuses to use an index???
Date: 2003-12-30 19:44:53
Message-ID: 3FF1D5B5.6060603@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, everybody!

I just ran into a weird problem on 7.3.4.
Here is a simple testcase:

rapidb=# create table nametab (name text);
CREATE TABLE
rapidb=# create index name_idx on nametab(name);
CREATE INDEX
rapidb=# set enable_seqscan=false;
SET
rapidb=# set enable_sort=false;
SET
rapidb=# explain select * from nametab where name like 'blah%';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on nametab (cost=100000000.00..100000022.50 rows=5 width=32)
Filter: (name ~~ 'blah%'::text)
(2 rows)

rapidb=# explain select * from nametab where name like 'blah%' order by name;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using name_idx on nametab (cost=0.00..54.50 rows=5 width=32)
Filter: (name ~~ 'blah%'::text)
(2 rows)

See - the first query wants to use seqscan, even though I am explicitly
telling it not to.
The second query does use the index for sorting (good), but still not
for the condition.

Does anyone have any idea what could be wrong here?
I'd greatly appreciate that...

Thanks a lot!

Dima.

P.S. I don't think this has anything to do with the table being empty -
first of all this is just a simple testcase, my real table has about 120
million rows (and I just analyzed it a few minutes ago).... also the
problem seems to only be with 'like' - if you replace 'like' with '=' in
the above query then it *will* use the index, even though the table is
still empty

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Russ Schneider 2003-12-30 19:54:27 7.3 dump into 7.2 (my solution)
Previous Message A E 2003-12-30 19:22:45 Quotes in dynamic sql