Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 06F87633428 for ; Wed, 25 Feb 2009 20:03:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11523-04 for ; Wed, 25 Feb 2009 20:03:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sabrina.lunarpages.com (sabrina.lunarpages.com [74.50.28.195]) by mail.postgresql.org (Postfix) with ESMTP id 93CD863339A for ; Wed, 25 Feb 2009 20:03:36 -0400 (AST) Received: from c-76-113-62-16.hsd1.nm.comcast.net ([76.113.62.16] helo=[192.168.0.101]) by sabrina.lunarpages.com with esmtpa (Exim 4.69) (envelope-from ) id 1LcTiv-00065K-CB for pgsql-admin@postgresql.org; Wed, 25 Feb 2009 16:03:33 -0800 Message-ID: <49A5DC53.7030107@djs-consulting.com> Date: Wed, 25 Feb 2009 17:03:31 -0700 From: "Daniel J. Summers" Organization: DJS Consulting User-Agent: Thunderbird 2.0.0.19 (X11/20090105) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: "like" and index References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com> <49A553B8.2040009@djs-consulting.com> <49A5B703.7080700@gmail.com> In-Reply-To: <49A5B703.7080700@gmail.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - sabrina.lunarpages.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - djs-consulting.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/221 X-Sequence-Number: 31442 Andrzej Zawadzki wrote: > Daniel J. Summers wrote: > >> Tony Liao wrote: >> >>> I try to explain analyze,but it doesn't work ,it use seq scan. >>> >> Generally speaking, LIKE doesn't use indexes. >> > ?! That's not true at all!! > MySQL will only use it if the wildcard isn't in the front (1) and requires the MATCH keyword to search full-text indexes (2), Oracle requires special "full-text" indexes to be able to use for LIKE (3) (actually dealt with that at work a few months back), SQL Server only uses it under certain conditions (4), and even PostgreSQL (the great subject of this mailing list) doesn't do it with a standard index (5) - you've got to use a special operator class. I know that Unisys RDMS doesn't look at indexes for a LIKE clause either, but most folks here will probably never use that. In my experience, the only times LIKE should be used is when the table being searched is small, performance doesn't matter, or there's not really any other way to get at the data. And, for the latter, there is usually some other way to get data if one thinks outside the box a bit; and, when there's not another way, the full-text or patterned indexes are the way to go. Performance-wise, it's a pitfall that you've got to ensure you know how to use. 1 - http://www.webmasterworld.com/forum88/9286.htm 2 - http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html 3 - http://www.dba-oracle.com/oracle_tips_like_sql_index.htm 4 - http://www.sql-server-performance.com/articles/dev/sql_best_practices_p1.aspx 5 - http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html -- Daniel J. Summers *Owner, DJS Consulting* Support • Tech Blog daniel@djs-consulting.com • http://www.djs-consulting.com GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ y++++