pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Searching in a string with index
2+ messages / 2 participants
[nested] [flat]

* Searching in a string with index
@ 2007-07-25 13:17 Alexander B. <burbello3000@yahoo.com.br>
  2007-07-25 17:07 ` Re: Searching in a string with index Igor Neyman <ineyman@perceptron.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Alexander B. @ 2007-07-25 13:17 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Searching in a string with index
  2007-07-25 13:17 Searching in a string with index Alexander B. <burbello3000@yahoo.com.br>
@ 2007-07-25 17:07 ` Igor Neyman <ineyman@perceptron.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Igor Neyman @ 2007-07-25 17:07 UTC (permalink / raw)
  To: Alexander B. <burbello3000@yahoo.com.br>; 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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Alexander B.
Sent: Wednesday, July 25, 2007 9:18 AM
To: pgsql-admin@postgresql.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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-07-25 17:07 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-25 13:17 Searching in a string with index Alexander B. <burbello3000@yahoo.com.br>
2007-07-25 17:07 ` Igor Neyman <ineyman@perceptron.com>

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