From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: "like" and index
Date: Wed, 25 Feb 2009 21:15:21 +0100
Message-ID: <pueixmgtnq.fsf@srv.protecting.net> (raw)
References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.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
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: hari.fuchs@gmail.com
Subject: Re: "like" and index
In-Reply-To: <pueixmgtnq.fsf@srv.protecting.net>
* 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