Message-ID: <49A553B8.2040009@djs-consulting.com>
Date: Wed, 25 Feb 2009 07:20:40 -0700
From: "Daniel J. Summers" <daniel@djs-consulting.com>
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>
In-Reply-To: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Tony Liao wrote:
> I try to explain analyze,but it doesn't work ,it use seq scan.
Generally speaking, LIKE doesn't use indexes. However, there are a 
couple of things you could try - definitely use EXPLAIN to see if this 
gets you the improvement you're looking for.

- You could try using = on the substring. I'm not sure whether this 
would use an index or not, but it'll accomplish the same think as using 
LIKE. Using your example,

SELECT id FROM table_a
WHERE substr(prefix, 1, length('johnsmith')) = 'johnsmith';

- You could use the BETWEEN clause instead - I know that BETWEEN uses 
indexes when possible.

SELECT id, prefix FROM table_a
WHERE prefix BETWEEN 'johnsmith' AND 'ZZZZZZZZZZZZZZZZZZZZZZZZ';

You'd have to write your application code to actually apply the 
"johnsmith" filter, and stop outputting results when the prefix ended - 
that's why I've added "prefix" to the select clause. Also, with the 
"Z"s, make that however many characters "prefix" is defined.

> ps:I have another table table_B would use 
> table_B.prefix=table_A.prefix.so <http://table_A.prefix.so> how can I 
> create the index?
If you're joining them, a regular index should get the job done.

CREATE INDEX idx_table_b_prefix ON table_b (prefix);

Then, when you're getting data...

SELECT [something]
FROM table_a a
INNER JOIN table_b b ON a.prefix = b.prefix
WHERE [some other condition]

The inner join will only select records where they match - i.e., there 
are rows in both tables with the same prefix. If you change "INNER" to 
"LEFT", you'll get the rows from table a, and if a match isn't found, 
the table b columns will be null. If you change "INNER" to "RIGHT", it's 
the opposite, but I've yet to find a good use for a right join other 
than confusing the next person to look at it. :)

-- 
Daniel J. Summers
*Owner, DJS Consulting* Support <http://support.djs-consulting.com/> • 
Tech Blog <http://www.djs-consulting.com/linux/blog>

daniel@djs-consulting.com <mailto:daniel@djs-consulting.com> • 
http://www.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++++