I have a table with a text column and I'd like to find entries matching
"pattern*" in that column. When using a between i get:

sd=> explain analyze select id,name, shortname from tr where shortname between 'Run_' and 'RunZ';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tr_shortname_idx on traces (cost=0.00..37.86 rows=10 width=42) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: ((shortname >= 'Run_'::text) AND (shortname <= 'RunZ'::text))
Total runtime: 0.052 ms
(3 rows)

But when using like:
sd=> explain analyze select id,name, shortname from tr where shortname like 'Run%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on tr (cost=0.00..42379.94 rows=10 width=42) (actual time=988.416..988.416 rows=0 loops=1)
Filter: (shortname ~~ 'Run%'::text)
Total runtime: 988.473 ms
(3 rows

Why doesn't it use an index on the "like" operator when it doesn't contain a wildcard in the beginning of the pattern?

Thanks.

--
../Jesper Krogh, jesper (AT) krogh (DOT) cc
Jabber ID: jesper (AT) jabbernet (DOT) dk



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Jesper,

How many rows are there in the table? What happens if you force an index
scan on the 2nd query? Is this ASCII text or UNICODE text?

Normally LIKE 'xxx%' does use an index if it's useful, so there is something
else going on here.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Jesper Krogh <jesper (AT) krogh (DOT) cc> writes:
Probably because your locale isn't C --- locale-specific sort ordering
usually isn't compatible with the needs of LIKE, so we can only make
that optimization in C locale.

You can either re-initdb in C locale, or (if you're using 7.4) create a
specialized index with non-locale-dependent comparison operators. See
the manual concerning specialized index operator classes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Josh Berkus wrote:

Shall not him define an index with the right operator class in order to be used
with the like operator ?



Regards
Gaetano Mendola

I gmane.comp.db.postgresql.novice, skrev Tom Lane:
Locale was set to:
lc_messages = 'en_US.iso885915' # locale for system error
message string
s
lc_monetary = 'en_US.iso885915' # locale for monetary formatting
lc_numeric = 'en_US.iso885915' # locale for number formatting
lc_time = 'en_US.iso885915' # locale for time
formatting

I'll try re-initdb to locale C in the weekend.. this requires dump and
restore right?


Jesper


--
../Jesper Krogh, jesper (AT) krogh (DOT) cc
Jabber ID: jesper (AT) jabbernet (DOT) dk



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster