Question about indexes.

From: "Spolar Alejandro" <alejo(at)mecon(dot)ar>
To: <pgsql-general(at)postgresql(dot)org>
Cc: <mario(at)mecon(dot)ar>, <jorge2(at)mecon(dot)ar>
Subject: Question about indexes.
Date: 1999-06-03 17:08:28
Message-ID: 000101beade3$b2bee930$458665a8@euler.mecon.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter T Mount 1999-06-03 17:21:41 Re: PostgreSQL Install
Previous Message Kapoor, Nishikant X 1999-06-03 15:20:07 RE: [GENERAL] RE: [PORTS] AIX-4.2.1 binaries ? more info. PLEASE !