use of index - Mailing list pgsql-general

Hi,

I've stapped over the following magic:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                             Table "public.users"
  Column  |     Type     |
Modifiers
----------+--------------+----------------------------------------------------
 id       | integer      | not null default
nextval('users_id_seq'::regclass)
 username | text         | not null
 firma    | integer      |
 email    | text         |
 state    | character(1) |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE, btree (username)
Foreign-key constraints:
    "users_firma_fkey" FOREIGN KEY (firma) REFERENCES firmy(id)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                             Table "public.ludzie"
  Column  |     Type     |
Modifiers
----------+--------------+-----------------------------------------------------
 id       | integer      | not null default
nextval('ludzie_id_seq'::regclass)
 username | text         | not null
 firma    | integer      |
 email    | text         |
 state    | character(1) |
Indexes:
    "ludzie_pkey" PRIMARY KEY, btree (id)
    "username_id_key" UNIQUE, btree (username)
Foreign-key constraints:
    "$1" FOREIGN KEY (firma) REFERENCES firmy(id)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INSERT INTO users SELECT * from ludzie;
INSERT 0 14
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
strop=# EXPLAIN ANALYZE SELECT * from users where username =
current_user;
                                                        QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------
 Index Scan using users_username_key on users  (cost=0.00..5.83 rows=1
width=80) (actual time=0.061..0.061 rows=0 loops=1)
   Index Cond: (username = ("current_user"())::text)
 Total runtime: 0.193 ms
(3 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
EXPLAIN ANALYZE SELECT * from ludzie where username = current_user;
                                           QUERY
PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on ludzie  (cost=0.00..1.19 rows=1 width=80) (actual
time=0.096..0.096 rows=0 loops=1)
   Filter: (username = ("current_user"())::text)
 Total runtime: 0.185 ms
(3 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

As fak as I can tell, both TABLE users and TABLE ludzie are identical:
1. they have the same structure
2. they have the same contents (users was empty before INSERT)
Still, the query plan on them differs, and quite signifficantly - one
uses index, while the other doesn't.

Can someone shred some light on why is that so?

Note: I've created "TABLE users" after noticing, that "TABLE ludzie" is
Seq-scanned over username. But I'm not able to reproduce the seq-scan on
the freshly created table, which is *identical* to "TABLE ludzie". On
the other hand, I have REINDEXED the database, to no avail.
ludzie(username) is still seq-scanned.

Any ideas?

--
Rafal Pietrak <rafal@poczta.homelinux.com>