From: Daniel J. Summers <daniel@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Re: "like" and index
Date: Wed, 25 Feb 2009 07:20:40 -0700
Message-ID: <49A553B8.2040009@djs-consulting.com> (raw)
In-Reply-To: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
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++++
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel@djs-consulting.com
Subject: Re: "like" and index
In-Reply-To: <49A553B8.2040009@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox