Hello,
This is because postgresql looks at the qyuery and thinks it would
be
faster to do a sequential scan rather than an index scan. This is because
index lookups are not free. This is probably due to id > 100 which I'm
guessing could match a lot of cases. using id>100 AND id<105 may be tight
enough to gain an index scan again.
You can try and force it to use index scans by:
SET enable_seqscan = false
(It might be informative to do this just to have a look at the difference in
query times).
If you are finding the choice between index and sequential scans to be of
then a less blunt approach is to alter the random_page_cost in
postmaster.conf.
Also make sure to analyse your db to update the statistics. In >=7.2 you can
just use ANALYSE. Before this you had to do VACUUM ANALYSE (which still
works). It is well worth regularly vacuuming anyway.
Hope this helps,
- Stuart
P.S. pgsql-general or pgsql-sql might be better lists for such questions.
-----Original Message-----
From: Andrzej Zeja [mailto:kinaz@eden.tu.kielce.pl]
Hi
I've got table Test and index for this table B-Tree type.
and
I've made query like:
Select * from test where Id<100;
During execution Optimizer makes no use of the index.
In documentation stands that using B-Tree index is used for such kind of
queries (with '>','<').
Everything works fine only for '=' comparision.
Why it doesn't work in my case?
Why Sequenced Scan is executed instead of indexed scan?
Below is output of my query:
template1=# explain select * from test where id > 100;
NOTICE: QUERY PLAN:
Seq Scan on test (cost=0.00..1.06 rows=3 width=18)
NOTICE: QUERY PLAN:
Seq Scan on test (cost=0.00..1.06 rows=3 width=18)
Andrzej