From: | <vivek(at)staff(dot)ownmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | select query not using index |
Date: | 2006-12-02 11:05:47 |
Message-ID: | 200612021105.kB2B5lWY017777@mail009.ownmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear Friends,
I have a table as
\d userpref;
Table "public.userpref"
Column | Type | Modifiers
-------------+------------------------+------------------------------------------------
username | character varying(101) | not null
email | character varying(255) | not null
context | character varying(32) | not null default 'from_box'::character varying
Indexes:
"userpref_user_idx" btree (username)
Foreign-key constraints:
"userpref_username_fkey" FOREIGN KEY (username, email) REFERENCES users(username, email)
The index was created before the table was populated. There are 3 rows in the table for 3 different users. Now when I do a
EXPLAIN SELECT * from userpref where username = 'vivek';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on userpref (cost=0.00..1.26 rows=1 width=349)
Filter: ((username)::text = 'vivek'::text)
EXPLAIN ANALYZE SELECT * from userpref where username = 'vivek';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on userpref (cost=0.00..1.04 rows=1 width=70) (actual time=0.060..0.071 rows=1 loops=1)
Filter: ((username)::text = 'vivek'::text)
Total runtime: 0.216 ms
(3 rows)
It shows seq scan . It is not using the index perhaps. But I fail to understand why does it not use the index created? I have tried vacuuming the database, reindexing the table, running analyze command.
Can anyone tell me what am I doing wrong?
With warm regards.
Vivek J. Joshi.
vivek(at)staff(dot)ownmail(dot)com
Trikon Electronics Pvt. Ltd.
All science is either physics or stamp collecting.
-- Ernest Rutherford
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory S. Williamson | 2006-12-02 12:28:11 | Re: select query not using index |
Previous Message | hubert depesz lubaczewski | 2006-12-02 10:14:51 | Re: postgresql 8.2 rc1 - crash |