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

Seq scan with a like operator

From: "Jouneau Luc" <Luc(dot)Jouneau(at)diamant(dot)jouy(dot)inra(dot)fr>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Seq scan with a like operator
Date: 2004-07-23 12:50:42
Message-ID: 006d01c470b3$aa0381f0$4703668a@pc502jlo (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,
I don't understand why with a like operator, PostgreSQL 7.4.2 does not behave the same as with an equal operator : 

Here's a table with about 200.000 tuples : 
CREATE TABLE public.annu_pers2
(
  nom varchar(50),
  prenom varchar(50),
  nom_int varchar(50),
  ....
) WITH OIDS;

CREATE INDEX personne_fk3
  ON public.annu_pers2
  USING btree
  (nom_int);

======
Here are the two explain analyze requests :
select nom_int
from annu_pers2 
where nom_int ='X'

Index Scan using personne_fk3 on annu_pers2  (cost=0.00..125.66 rows=31 width=11) (actual time=0.359..0.359 rows=0 loops=1)
  Index Cond: ((nom_int)::text = 'X'::text)
Total runtime: 0.490 ms
-------
select nom_int
from annu_pers2 
where nom_int like 'X'

Seq Scan on annu_pers2  (cost=0.00..5885.60 rows=31 width=11) (actual time=1213.594..1213.594 rows=0 loops=1)
  Filter: ((nom_int)::text ~~ 'X'::text)
Total runtime: 1213.729 ms

Could someone give me a reason of the sequential scan on the request with like operator
(I know that without % a like operator is not usefull, but it was to restrain as much as possible probability of mistakes - the problem remains the same with or without %).

Thanks for your answer(s).

Luc

Responses

pgsql-admin by date

Next:From: Terry HamptonDate: 2004-07-23 13:09:03
Subject: [Fwd: Re: HELP ! With Money type]
Previous:From: Terry HamptonDate: 2004-07-23 12:36:41
Subject: HELP ! With Money type

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