From: Jouneau Luc <Luc.Jouneau@diamant.jouy.inra.fr>
To: pgsql-admin@postgresql.org
Subject: Seq scan with a like operator
Date: Fri, 23 Jul 2004 14:50:42 +0200
Message-ID: <006d01c470b3$aa0381f0$4703668a@pc502jlo> (raw)
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: Luc.Jouneau@diamant.jouy.inra.fr
Subject: Re: Seq scan with a like operator
In-Reply-To: <006d01c470b3$aa0381f0$4703668a@pc502jlo>
References: <006d01c470b3$aa0381f0$4703668a@pc502jlo>
* 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