Обсуждение: 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

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!
-------------------------------------------------------

am  Sat, dem 02.12.2006, um 16:35:47 +0530 mailte vivek@staff.ownmail.com folgendes:
>
> 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

In this case, with only 3 rows, it is much cheaper to do a seq-scan
instead a index-scan, because a index-scan must read the index first and
then the table. And in your case, with only 3 rows, the engine needs
only one page to read.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Yes , that was the case indeed. I disabled seq scan and it used the index. And the cost was higher than seq scan.

Thanks a lot for all your replies.

With warm regards.

Vivek J. Joshi.

vivek@staff.ownmail.com
Trikon Electronics Pvt. Ltd.

All science is either physics or stamp collecting.
                -- Ernest Rutherford



A. Kretschmer wrote:
> >am  Sat, dem 02.12.2006, um 16:35:47 +0530 mailte vivek@staff.ownmail.com
folgendes:
>>
>> 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
>
>In this case, with only 3 rows, it is much cheaper to do a seq-scan
>instead a index-scan, because a index-scan must read the index first and
>then the table. And in your case, with only 3 rows, the engine needs
>only one page to read.
>
>
>
>Andreas
>--
>Andreas Kretschmer
>Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
>GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster