Re: select query not using index - Mailing list 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!
-------------------------------------------------------