Thread: 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
On 12/2/06, vivek@staff.ownmail.com <vivek@staff.ownmail.com> wrote:
postgresql will not use index scan for table with 3 rows in it. it is way faster to use seq scan on it.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
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
postgresql will not use index scan for table with 3 rows in it. it is way faster to use seq scan on it.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
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