From ssutjiono@wc-group.com Fri Feb 22 14:51:10 2002 Received: from qmsql.netservers.net (hpmail1.netservers.net [209.196.128.163]) by postgresql.org (Postfix) with SMTP id 3FAFB475B20 for ; Fri, 22 Feb 2002 09:51:10 -0500 (EST) Received: (qmail 2047 invoked from network); 22 Feb 2002 14:51:14 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail1.netservers.net with SMTP; 22 Feb 2002 14:51:14 -0000 Message-ID: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: Subject: How does Index Scan get used Date: Fri, 22 Feb 2002 09:46:30 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0185_01C1BB85.CE1E47E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/310 X-Sequence-Number: 6732 This is a multi-part message in MIME format. ------=_NextPart_000_0185_01C1BB85.CE1E47E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 I tried an Index Scan but I kept on getting Seq Scan. Do you know why ? Wha= t do I need to do to get my query to use index scan (I tried to turn it on = by doing SET ENABLE_indexscan =3D on; but it didn't work) create table test (test_col text); create index test_index on test (test_col); insert into test values ('abc.xyz'); insert into test values ('abcxyz'); insert into test values ('abc/xyz'); explain select * from test where test_col like 'abc/%'; NOTICE: QUERY PLAN: Seq Scan on test (cost=3D0.00..22.50 rows=3D10 width=3D12) Another question: Does Regex (~*) or like use table scan ? Thanks, Sam _________________________________________________ Expand your wireless world with Arkdom PLUS http://www.arkdom.com/ ------=_NextPart_000_0185_01C1BB85.CE1E47E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
I tried an Index Scan but I kept on g= etting=20 Seq Scan. Do you know why ? What do I need to do to get my query = to=20 use index scan (I tried to turn it on  by doing SET ENABLE_indexscan = =3D on;=20 but it didn't work)
 
create table test (test_col text);
crea= te index test_index on t= est=20 (test_col);
insert into test values ('abc.xyz');
insert into test val= ues=20 ('abcxyz');
insert into test values ('abc/xyz');
explain select * from test where test_col = like=20 'abc/%';
 
NOTICE:  QUERY PLAN:
 
Seq Scan on test&nb= sp;=20 (cost=3D0.00..22.50 rows=3D10 width=3D12)
 
Another question:
Does Regex (~*) or like use table scan=20 ?
 
Thanks,
Sam
_________________________________________________
 Expand = your=20 wireless world with Arkdom PLUS
 http://www.arkdom.com/
= ------=_NextPart_000_0185_01C1BB85.CE1E47E0-- From sszabo@megazone23.bigpanda.com Fri Feb 22 16:12:58 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 134FD4767C2 for ; Fri, 22 Feb 2002 11:12:58 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id DF995D600; Fri, 22 Feb 2002 08:13:02 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D51045BF9; Fri, 22 Feb 2002 08:13:02 -0800 (PST) Date: Fri, 22 Feb 2002 08:13:02 -0800 (PST) From: Stephan Szabo To: "Samuel J. Sutjiono" Cc: Subject: Re: How does Index Scan get used In-Reply-To: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> Message-ID: <20020222081158.U35057-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/317 X-Sequence-Number: 6739 On Fri, 22 Feb 2002, Samuel J. Sutjiono wrote: > I tried an Index Scan but I kept on getting Seq Scan. Do you know why > ? What do I need to do to get my query to use index scan (I tried to > turn it on by doing SET ENABLE_indexscan = on; but it didn't work) > > create table test (test_col text); > create index test_index on test (test_col); > insert into test values ('abc.xyz'); > insert into test values ('abcxyz'); > insert into test values ('abc/xyz'); > > explain select * from test where test_col like 'abc/%'; > > NOTICE: QUERY PLAN: > > Seq Scan on test (cost=0.00..22.50 rows=10 width=12) > > Another question: > Does Regex (~*) or like use table scan ? It may, but IIRC only if you're running in 'C' locale (the rule being used to make it indexable doesn't necessarily work in other locales). From olly@lfix.co.uk Fri Feb 22 18:05:02 2002 Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by postgresql.org (Postfix) with ESMTP id 675C3476152 for ; Fri, 22 Feb 2002 13:05:02 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-33.mail.demon.net with esmtp (Exim 3.34 #1) id 16eK44-0003ta-0X; Fri, 22 Feb 2002 18:05:00 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16eK7Q-00053T-00; Fri, 22 Feb 2002 18:08:28 +0000 Subject: Re: How does Index Scan get used From: Oliver Elphick To: "Samuel J. Sutjiono" Cc: pgsql-sql@postgresql.org In-Reply-To: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> References: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 22 Feb 2002 18:08:27 +0000 Message-Id: <1014401308.13240.2302.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/321 X-Sequence-Number: 6743 On Fri, 2002-02-22 at 14:46, Samuel J. Sutjiono wrote: > > I tried an Index Scan but I kept on getting Seq Scan. Do you know why ? What do I need to do to get my query to use index scan (I tried to turn it on by doing SET ENABLE_indexscan = on; but it didn't work) > > create table test (test_col text); > create index test_index on test (test_col); > insert into test values ('abc.xyz'); > insert into test values ('abcxyz'); > insert into test values ('abc/xyz'); > > explain select * from test where test_col like 'abc/%'; > > NOTICE: QUERY PLAN: > > Seq Scan on test (cost=0.00..22.50 rows=10 width=12) PostgreSQL thinks there are only 10 rows in the table. Therefore it will not choose an index scan because for so few rows it is bound to be more expensive than a sequential scan. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26 From olly@lfix.co.uk Fri Feb 22 19:29:25 2002 Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (Postfix) with ESMTP id F32C7475A34 for ; Fri, 22 Feb 2002 14:29:25 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-30.mail.demon.net with esmtp (Exim 3.34 #1) id 16eLNl-000PqE-0U; Fri, 22 Feb 2002 19:29:25 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16eLR8-0007ce-00; Fri, 22 Feb 2002 19:32:54 +0000 Subject: Re: How does Index Scan get used From: Oliver Elphick To: Oliver Elphick Cc: "Samuel J. Sutjiono" , pgsql-sql@postgresql.org In-Reply-To: <1014401308.13240.2302.camel@linda> References: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> <1014401308.13240.2302.camel@linda> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 22 Feb 2002 19:32:53 +0000 Message-Id: <1014406374.13241.2406.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/324 X-Sequence-Number: 6746 On Fri, 2002-02-22 at 18:08, Oliver Elphick wrote: > PostgreSQL thinks there are only 10 rows in the table. Therefore it > will not choose an index scan because for so few rows it is bound to be > more expensive than a sequential scan. But then I saw Tom's mail saying that it does use the index. How is that? Surely these few values would all be brought in in one single disk read? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26 From fbax@sympatico.ca Fri Feb 22 20:17:19 2002 Received: from tomts16-srv.bellnexxia.net (tomts16.bellnexxia.net [209.226.175.4]) by postgresql.org (Postfix) with ESMTP id 21648475CB9 for ; Fri, 22 Feb 2002 15:17:19 -0500 (EST) Received: from ACER ([65.94.213.172]) by tomts16-srv.bellnexxia.net (InterMail vM.4.01.03.23 201-229-121-123-20010418) with SMTP id <20020222201719.QXLG23163.tomts16-srv.bellnexxia.net@ACER>; Fri, 22 Feb 2002 15:17:19 -0500 Message-Id: <3.0.6.32.20020222151337.0079ac40@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.6 (32) Date: Fri, 22 Feb 2002 15:13:37 -0500 To: "Samuel J. Sutjiono" From: Frank Bax Subject: Re: How does Index Scan get used Cc: In-Reply-To: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200202/325 X-Sequence-Number: 6747 LIKE works fine on my system (see regex notes below): fbax=# create table test (test_col text); CREATE fbax=# create index test_index on test (test_col); CREATE fbax=# insert into test values ('abc.xyz'); INSERT 6189442 1 fbax=# insert into test values ('abcxyz'); INSERT 6189443 1 fbax=# insert into test values ('abc/xyz'); INSERT 6189444 1 fbax=# explain select * from test where test_col like 'abc/%'; NOTICE: QUERY PLAN: Index Scan using test_index on test (cost=0.00..8.14 rows=10 width=12) EXPLAIN fbax=# select version(); version ------------------------------------------------------------------- PostgreSQL 7.1 on i386-unknown-openbsd2.9, compiled by GCC 2.95.3 (1 row) fbax=# create index test_lower_index on test (lower(test_col)); CREATE fbax=# explain select * from test where lower(test_col) ~ '^abc'; NOTICE: QUERY PLAN: Seq Scan on test (cost=0.00..1.04 rows=1 width=12) EXPLAIN fbax=# insert into test ( select surname from chl_name ); INSERT 0 419 fbax=# vacuum analyse test; VACUUM fbax=# explain select * from test where lower(test_col) ~ '^abc'; NOTICE: QUERY PLAN: Index Scan using test_lower_index on test (cost=0.00..4.07 rows=4 width=12) EXPLAIN ===== ===== ===== ===== ===== regex ~* won't use index, because of the case-insensitive stuff. The above example only works when you anchor search to beginning of field using '^'. Frank At 09:46 AM 2/22/02 -0500, Samuel J. Sutjiono wrote: >I tried an Index Scan but I kept on getting Seq Scan. >Do you know why ? What do I need to do to get my query >to use index scan (I tried to turn it on by doing >SET ENABLE_indexscan = on; but it didn't work) > by doing SET ENABLE_indexscan = on; but it didn't work) >create table test (test_col text); >create index test_index on test (test_col); >insert into test values ('abc.xyz'); >insert into test values ('abcxyz'); >insert into test values ('abc/xyz'); > explain select * from test where test_col like 'abc/%'; >QUERY PLAN: >Seq Scan (cost=0.00..22.50 rows=10 width=12) >Another question: Does >Regex (~*) or like use table scan ? Thanks, Sam >_________________________________________________ > Expand your wireless world with Arkdom PLUS > http://www.arkdom.com/ > From tgl@sss.pgh.pa.us Sat Feb 23 00:11:54 2002 Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 5805C475461 for ; Fri, 22 Feb 2002 19:11:54 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1N0BlS21704; Fri, 22 Feb 2002 19:11:47 -0500 (EST) To: Oliver Elphick Cc: "Samuel J. Sutjiono" , pgsql-sql@postgresql.org Subject: Re: How does Index Scan get used In-reply-to: <1014406374.13241.2406.camel@linda> References: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> <1014401308.13240.2302.camel@linda> <1014406374.13241.2406.camel@linda> Comments: In-reply-to Oliver Elphick message dated "22 Feb 2002 19:32:53 +0000" Date: Fri, 22 Feb 2002 19:11:47 -0500 Message-ID: <21701.1014423107@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/331 X-Sequence-Number: 6753 Oliver Elphick writes: > On Fri, 2002-02-22 at 18:08, Oliver Elphick wrote: >> PostgreSQL thinks there are only 10 rows in the table. Therefore it >> will not choose an index scan because for so few rows it is bound to be >> more expensive than a sequential scan. > But then I saw Tom's mail saying that it does use the index. How is > that? Surely these few values would all be brought in in one single > disk read? Well, there are two different issues here. One is whether the query *can* use the index --- that depends on your locale, as I pointed out, and also on the details of the LIKE or regex pattern being used, as Frank Bax points out nearby. The other issue is whether the planner *will choose to* use the index, when it has a choice. Oliver is quite correct that the planner would not choose to use the index on such a small table --- if it knows the table is small. But in these dummy examples that we're discussing, we just created the table and index and then put a few values in (the order is important BTW), and we never vacuumed. In this scenario the table size estimates in pg_class have never been changed from the initial dummy values that CREATE TABLE puts in --- and those dummy values are deliberately set large enough to allow index scans. (10 pages / 1000 rows, IIRC.) We don't initialize the size estimates to zero, because if we did, the planner would make totally foolish choices for never-vacuumed tables ... which could actually contain lots of data. VACUUM the test table, and it'll stop using the index, unless you put a lot more test data in than we've used in these examples. Oh BTW: the "10 rows" Oliver was wondering about are not the number of rows the planner thinks are in the table; they're the estimated number of result rows. Since there's no VACUUM ANALYZE stats available either, this is just a default selectivity estimate for the match clause (0.01) times the initial dummy reltuples value (1000). Not much content in it at all, eh? regards, tom lane From olly@lfix.co.uk Sat Feb 23 00:52:30 2002 Received: from tele-post-20.mail.demon.net (tele-post-20.mail.demon.net [194.217.242.20]) by postgresql.org (Postfix) with ESMTP id A15CB475E41 for ; Fri, 22 Feb 2002 19:52:30 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by tele-post-20.mail.demon.net with esmtp (Exim 3.34 #1) id 16eQQS-0009fe-0K; Sat, 23 Feb 2002 00:52:32 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16eQTq-0000lE-00; Sat, 23 Feb 2002 00:56:02 +0000 Subject: Re: How does Index Scan get used From: Oliver Elphick To: Tom Lane Cc: pgsql-sql@postgresql.org In-Reply-To: <21701.1014423107@sss.pgh.pa.us> References: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com> <1014401308.13240.2302.camel@linda> <1014406374.13241.2406.camel@linda> <21701.1014423107@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 23 Feb 2002 00:56:01 +0000 Message-Id: <1014425762.13241.2733.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/333 X-Sequence-Number: 6755 On Sat, 2002-02-23 at 00:11, Tom Lane wrote: ... > Oh BTW: the "10 rows" Oliver was wondering about are not the number of > rows the planner thinks are in the table; they're the estimated number > of result rows. Since there's no VACUUM ANALYZE stats available either, > this is just a default selectivity estimate for the match clause (0.01) > times the initial dummy reltuples value (1000). Not much content in it > at all, eh? Thanks, Tom. That was most informative. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26