Обсуждение: "like" and index

hi all,
      I have a table table_A (id serial,prefix varchar),for example.
      now I want to get the id of "johnsmith"'s prefix match table_A.prefix,so I do select id from table_A where 'johnsmith' like prefix||'%'  ,the table_A is very large so I would like to make index. create table_A_index on table_A(prefix)
      I try to explain analyze,but it doesn't work ,it use seq scan.
      I try another index. drop index table_A_index; create table_A_index on table_A(prefix varchar_pattern_ops); it doesn't work,too.
      thanks
       ps:I have another table table_B would use table_B.prefix=table_A.prefix.so how can I create the index?





           yours, Tony

On Mittwoch 25 Februar 2009 Tony Liao wrote:
> hi all,
>       I have a table table_A (id serial,prefix varchar),for example.
>       now I want to get the id of "johnsmith"'s prefix match
> table_A.prefix,so I do select id from table_A where 'johnsmith' like
> prefix||'%'

SELECT id FROM table_A WHERE prefix LIKE 'johnsmith%';

> ,the table_A is very large so I would like to make
> index. create table_A_index on table_A(prefix)
>       I try to explain analyze,but it doesn't work ,it use seq scan.

Because your SELECT was wrong.

>       I try another index. drop index table_A_index; create
> table_A_index on table_A(prefix varchar_pattern_ops); it doesn't
> work,too.
>       thanks
>        ps:I have another table table_B would use table_B.prefix=
> table_A.prefix.so how can I create the index?

That's a foreign key, if I understand you correctly.

ALTER TABLE ONLY B
    ADD CONSTRAINT B_prefix_fkey FOREIGN KEY (prefix) REFERENCES
A(prefix) ON UPDATE CASCADE ON DELETE CASCADE;

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

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++++

In article <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>,
Tony Liao <tonyliao@yuehetone.com> writes:

> hi all,
>       I have a table table_A (id serial,prefix varchar),for example.
>       now I want to get the id of "johnsmith"'s prefix match table_A.prefix,so
> I do select id from table_A where 'johnsmith' like prefix||'%'  ,the table_A is
> very large so I would like to make index. create table_A_index on table_A
> (prefix)
>       I try to explain analyze,but it doesn't work ,it use seq scan.
>       I try another index. drop index table_A_index; create table_A_index on
> table_A(prefix varchar_pattern_ops); it doesn't work,too.

If I understand you correctly, the "prefix" contrib package is what
you need:

  CREATE TABLE tableA (
    id serial NOT NULL,
    prefix prefix_range NOT NULL,
    PRIMARY KEY (id)
  );

  CREATE INDEX tableA_prefix_ix on tableA
  USING gist (prefix gist_prefix_range_ops);

  COPY tableA (prefix) FROM stdin;
  john
  tom
  anne
  jim
  \.

  INSERT INTO tableA (prefix)
  SELECT x || 'test'
  FROM generate_series (1, 10000) g(x);

  ANALYZE tableA;

  EXPLAIN ANALYZE
  SELECT id, prefix
  FROM tableA
  WHERE prefix @> 'johnsmith';

will return something like that:

 Bitmap Heap Scan on tablea  (cost=4.33..32.10 rows=10 width=19) (actual time=0.035..0.036 rows=1 loops=1)
   Recheck Cond: (prefix @> 'johnsmith[]'::prefix_range)
   ->  Bitmap Index Scan on tablea_prefix_ix  (cost=0.00..4.33 rows=10 width=0) (actual time=0.026..0.026 rows=1
loops=1)
         Index Cond: (prefix @> 'johnsmith[]'::prefix_range)
 Total runtime: 0.133 ms

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!!

--
Andrzej Zawadzki

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 <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++++

"Daniel J. Summers" <daniel@djs-consulting.com> writes:
> 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.

Only if you're using a non-C locale.

            regards, tom lane

thanks,everybody!
           Harald,you understand correctly.I downloaded the prefix contrib and install,by the way,it seems that the prefix has bugs,I had to modify the Makefile and then install success.
           and then,I imported the prefix.sql.I did a test,but I found I can't create index of gist_prefix_range_ops,not found! I can insert the column prefix with type prefix_range.
           ps : doest it work in C local,or uft8?

hi Harald,
      I reboot the machine and create index,it works.thanks.

Daniel J. Summers wrote:
> 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!!
>>
[...]
> 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.

Please try... with "C" locales. Works out of the box.

--
Andrzej Zawadzki