pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow does Index Scan get used
7+ messages / 5 participants
[nested] [flat]
* How does Index Scan get used
@ 2002-02-22 14:46 Samuel J. Sutjiono <ssutjiono@wc-group.com>
0 siblings, 3 replies; 7+ messages in thread
From: Samuel J. Sutjiono @ 2002-02-22 14:46 UTC (permalink / raw)
To: pgsql-sql
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 ?
Thanks,
Sam
_________________________________________________
Expand your wireless world with Arkdom PLUS
http://www.arkdom.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-22 16:13 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
2 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-02-22 16:13 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql
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).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-22 18:08 Oliver Elphick <olly@lfix.co.uk>
parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
2 siblings, 1 reply; 7+ messages in thread
From: Oliver Elphick @ 2002-02-22 18:08 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-22 19:32 Oliver Elphick <olly@lfix.co.uk>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Oliver Elphick @ 2002-02-22 19:32 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-22 20:13 Frank Bax <fbax@sympatico.ca>
parent: Samuel J. Sutjiono <ssutjiono@wc-group.com>
2 siblings, 0 replies; 7+ messages in thread
From: Frank Bax @ 2002-02-22 20:13 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql
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/
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-23 00:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2002-02-23 00:11 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-sql
Oliver Elphick <olly@lfix.co.uk> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How does Index Scan get used
@ 2002-02-23 00:56 Oliver Elphick <olly@lfix.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Oliver Elphick @ 2002-02-23 00:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-02-23 00:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-22 14:46 How does Index Scan get used Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-22 16:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-22 18:08 ` Oliver Elphick <olly@lfix.co.uk>
2002-02-22 19:32 ` Oliver Elphick <olly@lfix.co.uk>
2002-02-23 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-23 00:56 ` Oliver Elphick <olly@lfix.co.uk>
2002-02-22 20:13 ` Frank Bax <fbax@sympatico.ca>
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