Re: select query not using index
От | Gregory S. Williamson |
---|---|
Тема | Re: select query not using index |
Дата | |
Msg-id | 71E37EF6B7DCC1499CEA0316A256832802B3E988@loki.wc.globexplorer.net обсуждение исходный текст |
Ответ на | select query not using index (<vivek@staff.ownmail.com>) |
Список | pgsql-general |
Vivek -- If you could let people know what version of postgres, and which OS, it might help. A guess: the planner sees that there are very few rows and decides that a sequential scan is faster (this is because a sequentialscan on a table with only a few rows is probably done in one operation; retrieving index values and the actualdata rows involves more trips to disk, potentially. You could test this by turning off seq scan as a user option andre-running the query. I note that it is casting "vivek" as text and the underlying column varchar; in earlier versions of postgres this might causea mismatch and confuse the planner; try casting as "WHERE username = 'vivek'::varchar" and see if that is an improvement. HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: pgsql-general-owner@postgresql.org on behalf of vivek@staff.ownmail.com Sent: Sat 12/2/2006 3:05 AM To: pgsql-general@postgresql.org Cc: Subject: [GENERAL] select query not using index 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 doa 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? Ihave 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@staff.ownmail.com Trikon Electronics Pvt. Ltd. All science is either physics or stamp collecting. -- Ernest Rutherford ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster ------------------------------------------------------- Click link below if it is SPAM gsw@globexplorer.com "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=457169e3251904846743324&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1" !DSPAM:457169e3251904846743324! -------------------------------------------------------
В списке pgsql-general по дате отправления: