Re: [CYGWIN] Using B-Tree index for such kind of queries (with '>', '<')

-----Original Message-----
From: pgsql-cygwin-owner(at)postgresql(dot)org
[mailto:pgsql-cygwin-owner(at)postgresql(dot)org]On Behalf Of Andrzej Zeja
Sent: Monday, May 06, 2002 9:39 AM
To: pgsql-cygwin(at)postgresql(dot)org
Subject: [CYGWIN] Using B-Tree index for such kind of queries (with
'>','<')

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)

The CYGWIN list is for issues specific to using PG on Windows/CYGWIN. This
question is better asked in pgsql-general. I've moved this response to that
list.

PG doesn't always use indexes, if the optimizer things sequential scans
are more likely. Probably (a) you don't have much data in the table, (b) you
haven't done a VACUUM ANALYZE in a while (to update table stats), or (c) PG
thinks that many of the rows will be <100.

Can you tell us more about the table, its data, and when you last
VACUUMed?

J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant