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
