Skip site navigation (1) Skip section navigation (2)

Re: Searching in a string with index

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Alexander B(dot)" <burbello3000(at)yahoo(dot)com(dot)br>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Searching in a string with index
Date: 2007-07-25 17:07:35
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A20216A15E@EXCHANGE.corp.perceptron.com (view raw or flat)
Thread:
Lists: pgsql-admin
Index will not be used if your string has "wild card" (%) in the
beginning.
It should be used, if you change your query to:
 
select *
from tb_gen_person
where pes_name like 'albert%';

Igor

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Alexander B.
Sent: Wednesday, July 25, 2007 9:18 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Searching in a string with index

Hi,
When I search some words into a varchar column and this column has an
index.
Does Postgres use index??

The search is done this way:

create table tb_gen_person (
  pes_nro_doc numeric(14) not null,
  pes_name varchar(150) not null,
  constraint pk_person PRIMARY KEY(pes_nro_doc) ); create index
ix_person_01 on tb_gen_person (pes_name);

select *
from tb_gen_person
where pes_name like '%albert%';

Not considering upper case, or tsearch2, this type of search use index
somehow?
I overhear that if the search has more than 5 characters, probably would
use index!! Is this true?

Thanks

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate


In response to

pgsql-admin by date

Next:From: Jessica RichardDate: 2007-07-25 17:20:45
Subject: primary key and foreign key
Previous:From: Abraham, DannyDate: 2007-07-25 16:58:28
Subject: Running "create database" using dblink_exe and gets locked forever

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group