From: Tony Liao <tonyliao@yuehetone.com>
To: Harald Fuchs <hari.fuchs@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: "like" and index
Date: Thu, 26 Feb 2009 13:24:17 +0800
Message-ID: <8f750b7c0902252124k70361392x1992852c1e76e04d@mail.gmail.com> (raw)
In-Reply-To: <pueixmgtnq.fsf@srv.protecting.net>
References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
<pueixmgtnq.fsf@srv.protecting.net>
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?
2009/2/26 Harald Fuchs <hari.fuchs@gmail.com>
> 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
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
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: tonyliao@yuehetone.com, hari.fuchs@gmail.com
Subject: Re: "like" and index
In-Reply-To: <8f750b7c0902252124k70361392x1992852c1e76e04d@mail.gmail.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