To: pgsql-admin@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: "like" and index
Date: Wed, 25 Feb 2009 21:15:21 +0100
Lines: 50
Message-ID: <pueixmgtnq.fsf@srv.protecting.net>
References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-15
Content-Transfer-Encoding: 8bit
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:E6VivljZEX1QpFw//1RqTF4ujgg=

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