Обсуждение: best index for ~ ordering?
Hi all, i have a large table with one varchar field, and im triyng to get information about what index should i use in order to using that index (if this is possible) for ~ searching, and/or using LIKE searching. Thanks! Gerardo
On Tue, Mar 06, 2007 at 06:34:23PM -0300, Gerardo Herzig wrote: > Hi all, i have a large table with one varchar field, and im triyng to > get information about what index should i use in order to using that > index (if this is possible) for ~ searching, and/or using LIKE searching. What sort of match are you doing? If you're doing left-anchored searches (LIKE 'blah%') then your standard btree is good. If you're doing unanchored searches (LIKE '%blah%' or similar) you're best doing tsearch. If it's right-anchored, you can do an index on the reverse of the string. A -- Andrew Sullivan | ajs@crankycanuck.ca Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris
Well, im wondering if is possible using LIKE '%blah%', even better would be upper/lower(string) like '%blah%', Im not at work right now, i will try it latter and makes you know about the results of using tsearch indexing. Thanks a lot, man! Gerardo > On Tue, Mar 06, 2007 at 06:34:23PM -0300, Gerardo Herzig wrote: >> Hi all, i have a large table with one varchar field, and im triyng to >> get information about what index should i use in order to using that >> index (if this is possible) for ~ searching, and/or using LIKE >> searching. > > What sort of match are you doing? If you're doing left-anchored > searches (LIKE 'blah%') then your standard btree is good. If you're > doing unanchored searches (LIKE '%blah%' or similar) you're best doing > tsearch. If it's right-anchored, you can do an index on the reverse > of the string. > > A > > -- > Andrew Sullivan | ajs@crankycanuck.ca > Unfortunately reformatting the Internet is a little more painful > than reformatting your hard drive when it gets out of whack. > --Scott Morris > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A.
On Wed, Mar 07, 2007 at 12:28:57AM -0300, gherzig@fmed.uba.ar wrote: > Well, im wondering if is possible using LIKE '%blah%', even better would be > upper/lower(string) like '%blah%', Your better bet then is either to index lower(string) or better, on insert convert everything to lower(). A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner