Re: [GENERAL] select query not using index
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. [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] select query not using index
On 12/2/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: 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
Re: [GENERAL] select query not using index
am Sat, dem 02.12.2006, um 16:35:47 +0530 mailte [EMAIL PROTECTED] 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
Re: [GENERAL] select query not using index
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 sequential scan on a table with only a few rows is probably done in one operation; retrieving index values and the actual data rows involves more trips to disk, potentially. You could test this by turning off seq scan as a user option and re-running the query. I note that it is casting "vivek" as text and the underlying column varchar; in earlier versions of postgres this might cause a 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: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED] 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 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. [EMAIL PROTECTED] 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 [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=457169e3251904846743324&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:457169e3251904846743324! --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[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 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. [EMAIL PROTECTED] 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
