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: (view raw, whole thread or download thread mbox)
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);
rapidb=# create index name_idx on nametab(name);
rapidb=#  set enable_seqscan=false;
rapidb=# set enable_sort=false;
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!


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


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-2017 The PostgreSQL Global Development Group