Обсуждение: Question about indexes.

Postgres Version : 6.5 (beta)
I have a table with 53411 rows.
This table has no indexes at all. Each query made (using EXPLAIN) reveals a
"Seq Scan".
So far, everything is OK.
Then I define 2 indexes, one affects a varchar field, for example 'lastname'
an the other a float type field, let´s call it 'id'.
When I perform a query such as SELECT * FROM table WHERE lastname =
'Douglas', EXPLAIN reveals that index is being used ("Index Scan").
But when the query is like: SELECT * FROM table WHERE id = 10, no index is
used, a "Seq Scan" is made. So it looks like if an index defined on a
numeric type field, doesn't work. (same thing happens with an index on
integer field).
Finally I made the index on 'id' field CLUSTERED. Repeating the query using
EXPLAIN I get a lower cost and "Index Scan".

The same test was made after deleting all records from the table and adding
a few ones (for example 100). This time a "non-clustered" index defined on
'id' field worked fine.

Is there an explanation of this behaviour?
Thanks in advance.

Spolar Alejandro.

> This table has no indexes at all. Each query made (using EXPLAIN) reveals a
> "Seq Scan".
> So far, everything is OK.
> Then I define 2 indexes, one affects a varchar field, for example 'lastname'
> an the other a float type field, let´s call it 'id'.
> When I perform a query such as SELECT * FROM table WHERE lastname =
> 'Douglas', EXPLAIN reveals that index is being used ("Index Scan").
> But when the query is like: SELECT * FROM table WHERE id = 10, no index is
> used, a "Seq Scan" is made. So it looks like if an index defined on a
> numeric type field, doesn't work. (same thing happens with an index on
> integer field).
> Finally I made the index on 'id' field CLUSTERED. Repeating the query using
> EXPLAIN I get a lower cost and "Index Scan".

i found a similar problem with 6.5beta, only i was using index fields of
int8, float8, datestamp and abstime.

be nice to know what the issue is.

jim (wandering off to look up "CLUSTERED")

--
[ Jim Mercer    Reptilian Research      jim@reptiles.org   +1 416 410-5633 ]
[ The telephone, for those of you who  have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail.            ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]