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/ >