Skip site navigation (1) Skip section navigation (2)

'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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group