Btree doesn't work with ">" condition

Hi,
I'm testing PostgreSQL, version 7.1.2, and I create a table, test1, with these kind of fields:

CREATE TABLE test1 (
id integer,
content varchar
);

with 800.000 records, and a btree index setted on the id field.

I noticed the query uses the index for "=" and "<" operators, but if the value used for the "<" operator is higher (600.000 for example), the query makes a seq scan of the table, and the index never works with the ">" operator. Am I managing the indexes well ?

Thanks for the help.

Ciao Paolo

Paolo Cassago

TalentManager
Milan, Paris, Madrid

Tel: +39 02 83 11 23 1
Fax: +39 02 700 43 99 81
Mob: +39 348 82 155 81

On 16 Nov 2001, Paolo Cassago wrote:

I'm testing PostgreSQL, version 7.1.2, and I create a table,
test1, with these kind of fields:

CREATE TABLE test1 (
id integer,
content varchar
);

with 800.000 records, and a btree index setted on the id field.

I noticed the query uses the index for "=" and "<" operators, but if
the value used for the "<" operator is higher (600.000 for example),
the query makes a seq scan of the table, and the index never works
with the ">" operator. Am I managing the indexes well ?

Let's go through the standard things. :) Have you vacuum analyzed
the table? What does explain show for the queries (particularly the
row count). If the number of rows grabbed is a reasonable percentage
of the table, currently sequence scan *may* be faster than an index
scan, so this may not be incorrect.

Paolo Cassago wrote:

Hi,
I'm testing PostgreSQL, version 7.1.2, and I create a table, test1, with these kind of fields:

CREATE TABLE test1 (
id integer,
content varchar
);

with 800.000 records, and a btree index setted on the id field.

I noticed the query uses the index for "=" and "<" operators, but if the value used for the "<" operator is higher (600.000 for example), the query makes a seq scan of the table, and the index never works with the ">" operator. Am I managing the indexes well ?

Have you run VACUUM ANALYZE lately ?

What does EXPLAIN think of the costs ?

-------------
Hannu