X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3EB7C9DCA07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Feb 2006 16:12:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36940-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Feb 2006 16:12:19 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.202])
	by postgresql.org (Postfix) with ESMTP id 7CF2C9DCA5E
	for <pgsql-performance@postgresql.org>;
	Tue, 21 Feb 2006 16:12:16 -0400 (AST)
Received: by zproxy.gmail.com with SMTP id v1so1325985nzb
	for <pgsql-performance@postgresql.org>;
	Tue, 21 Feb 2006 12:12:17 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=jp0wAxE1Q6z9yIZYosUu38pMfuY9Ov/OzylyPRfwyHRCa5OVCdAd7E0px7hAT/Xg0wDS4MgloMgyk68TWes2rBCBrDAnh2oeBxhTBgsCRwroEOq/JydQqG1j5PMAc5uuNE25DsXB+x49TcrcHaZs/GKCWob9CiAB7bcPONp/5Ic=
Received: by 10.36.90.14 with SMTP id n14mr1409436nzb;
	Tue, 21 Feb 2006 12:12:17 -0800 (PST)
Received: by 10.36.106.12 with HTTP; Tue, 21 Feb 2006 12:12:17 -0800 (PST)
Message-ID: <e4dcba670602211212i523ad54bh404bc1097f90ef6f@mail.gmail.com>
Date: Tue, 21 Feb 2006 22:12:17 +0200
From: "Ibrahim Tekin" <itekin@gmail.com>
To: "mark@mark.mielke.cc" <mark@mark.mielke.cc>
Subject: Re: LIKE query on indexes
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060221174048.GA8108@mark.mielke.cc>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_25273_32919795.1140552737301"
References: <e4dcba670602210757n2caf7c15t2a93353c9081c51f@mail.gmail.com>
	<20060221174048.GA8108@mark.mielke.cc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.44 required=5 tests=[AWL=0.439, HTML_MESSAGE=0.001]
X-Spam-Score: 0.44
X-Spam-Level: 
X-Archive-Number: 200602/332
X-Sequence-Number: 17318

------=_Part_25273_32919795.1140552737301
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

my database encoding is unicode.
i have two table, one is 3.64gb on hdd and has 2.2 million records. it take=
s
140 secs to run on my AMD Turion 64 M 800MHz/1GB laptop.
second table is 1.2gb, 220000 records, and takes 56 secs to run.

explain says 'Seq Scan on mytable, ..'

On 2/21/06, mark@mark.mielke.cc <mark@mark.mielke.cc> wrote:
>
> On Tue, Feb 21, 2006 at 05:57:12PM +0200, Ibrahim Tekin wrote:
> > i have btree index on a text type field. i want see rows which starts
> with
> > certain characters on that field. so i write a query like this:
> >     SELECT * FROM mytable WHERE myfield LIKE 'john%'
> > since this condition is from start of the field, query planner should
> use
> > index to find such elements but explain command shows me it will do a
> > sequential scan.
> > is this lack of a feature or i am wrong somewhere?
>
> Is the query fast enough? How big is your table? What does explain
> analyze select tell you?
>
> Cheers,
> mark
>
> --
> mark@mielke.cc / markm@ncf.ca / markm@nortel.com
> __________________________
> .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
> |\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
> |  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario,
> Canada
>
>   One ring to rule them all, one ring to find them, one ring to bring the=
m
> all
>                        and in the darkness bind them...
>
>                            http://mark.mielke.cc/
>
>

------=_Part_25273_32919795.1140552737301
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

my database encoding is unicode.<br>
i have two table, one is 3.64gb on hdd and has 2.2 million records. it take=
s 140 secs to run on my AMD Turion 64 M 800MHz/1GB laptop.<br>second table =
is 1.2gb, 220000 records, and takes 56 secs to run.<br><br>explain says 'Se=
q Scan on mytable, ..'
<br><br><div><span class=3D"gmail_quote">On 2/21/06, <b class=3D"gmail_send=
ername"><a href=3D"mailto:mark@mark.mielke.cc">mark@mark.mielke.cc</a></b> =
&lt;<a href=3D"mailto:mark@mark.mielke.cc">mark@mark.mielke.cc</a>&gt; wrot=
e:</span>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On Tue, Feb 21, 2=
006 at 05:57:12PM +0200, Ibrahim Tekin wrote:<br>&gt; i have btree index on=
 a text type field. i want see rows which starts with
<br>&gt; certain characters on that field. so i write a query like this:<br=
>&gt;&nbsp;&nbsp;&nbsp;&nbsp; SELECT * FROM mytable WHERE myfield LIKE 'joh=
n%'<br>&gt; since this condition is from start of the field, query planner =
should use<br>&gt; index to find such elements but explain command shows me=
 it will do a
<br>&gt; sequential scan.<br>&gt; is this lack of a feature or i am wrong s=
omewhere?<br><br>Is the query fast enough? How big is your table? What does=
 explain<br>analyze select tell you?<br><br>Cheers,<br>mark<br><br>--<br>
<a href=3D"mailto:mark@mielke.cc">mark@mielke.cc</a> / <a href=3D"mailto:ma=
rkm@ncf.ca">markm@ncf.ca</a> / <a href=3D"mailto:markm@nortel.com">markm@no=
rtel.com</a>&nbsp;&nbsp;&nbsp;&nbsp; __________________________<br>.&nbsp;&=
nbsp;.&nbsp;&nbsp;_&nbsp;&nbsp;._&nbsp;&nbsp;. .&nbsp;&nbsp; .__&nbsp;&nbsp=
;&nbsp;&nbsp;.&nbsp;&nbsp;. ._. .__ .&nbsp;&nbsp; . . .__&nbsp;&nbsp;| Neig=
hbourhood Coder
<br>|\/| |_| |_| |/&nbsp;&nbsp;&nbsp;&nbsp;|_&nbsp;&nbsp;&nbsp;&nbsp; |\/|&=
nbsp;&nbsp;|&nbsp;&nbsp;|_&nbsp;&nbsp;|&nbsp;&nbsp; |/&nbsp;&nbsp;|_&nbsp;&=
nbsp; |<br>|&nbsp;&nbsp;| | | | \ | \&nbsp;&nbsp; |__ .&nbsp;&nbsp;|&nbsp;&=
nbsp;| .|. |__ |__ | \ |__&nbsp;&nbsp;| Ottawa, Ontario, Canada<br><br>&nbs=
p;&nbsp;One ring to rule them all, one ring to find them, one ring to bring=
 them all
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and in the da=
rkness bind them...<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a href=3D"http://mark.mielke.cc/">http:/=
/mark.mielke.cc/</a><br><br></blockquote></div><br>

------=_Part_25273_32919795.1140552737301--