X-Original-To: pgsql-performance@postgresql.org Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 965A9475AFF for ; Thu, 20 Mar 2003 21:06:19 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h2L26Lub008916; Thu, 20 Mar 2003 21:06:21 -0500 (EST) To: josh@agliodbs.com Cc: "David Olbersen" , pgsql-performance@postgresql.org Subject: Re: Help with LIKE In-reply-to: <200303201727.21401.josh@agliodbs.com> References: <200303201727.21401.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Thu, 20 Mar 2003 17:27:21 -0800" Date: Thu, 20 Mar 2003 21:06:21 -0500 Message-ID: <8915.1048212381@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200303/147 X-Sequence-Number: 1445 Josh Berkus writes: >> SELECT * FROM table WHERE url ~ '^http://.*something.*$'; > That search still requires a seq scan, since it has "gaps" in the seqence of > characters. That is, > url ~ '^http://www.something.*' could use an index, but your search above > cannot. Actually, it *can* use an index ... but the index condition will only use the characters before the ".*", ie, "http://". Which is just about useless if you're searching a column of URLs :-( I agree that tsearch or OpenFTS are the tools to be looking at. regards, tom lane