Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (8.11.3/8.11.4) with SMTP id g1IKxQE41303 for ; Mon, 18 Feb 2002 15:59:26 -0500 (EST) (envelope-from chrup999@yahoo.com) Received: from chill-025.dfw.tx.bbnow.net (HELO ?192.168.0.6?) (24.219.2.25) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Feb 2002 20:59:25 -0000 Mime-Version: 1.0 X-Sender: chrup999@pop.mail.yahoo.com Message-Id: In-Reply-To: <00bd01c1b8ad$b9d03e90$6b97f181@speed> References: <00bd01c1b8ad$b9d03e90$6b97f181@speed> Date: Mon, 18 Feb 2002 14:59:22 -0600 To: "Kristian Eide" From: Chris Ruprecht Subject: Re: Why is my index not used Cc: Content-Type: text/plain; charset="iso-8859-1" ; format="flowed" Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/223 X-Sequence-Number: 6647 try to cast your argument, for example, if misscnt is of type bigint, try: explain select * from cam where misscnt>=1::int8; Best regards, Chris At 19:54 +0100 02/18/2002, Kristian Eide wrote: >I have a table, currently at about 150.000 rows, with a btree index on a >field named 'misscnt'. ~138k of the rows have this fields set as null, ~12k >as 0, ~350 as 1, ~200 as 2 and ~150 as 3 (these are the only values used). > >Still, I get the following: > ># explain select * from cam where misscnt>=1; >NOTICE: QUERY PLAN: > >Seq Scan on cam (cost=0.00..3609.59 rows=46896 width=66) > >Why do postgre think it will get over 46k rows from this query? If I try a >VACUUM ANALYZE on the table, I get: > ># explain select * from cam where misscnt>=1; >NOTICE: QUERY PLAN: > >Seq Scan on cam (cost=0.00..3874.01 rows=50347 width=66) > >So now it actually thinks it will get _more_ rows: > ># select count(*) from cam where misscnt>=1; > count >------- > 692 > >If I use "set enable_seqscan=false;" and try "select * from cam where >misscnt>=1;" the index is used, and the query executes quite a bit faster. >However, shouldn't Postgre be able to do this automatically? > >This is using PostgreSQL 7.1.2. > > >Thanks. > >--- >Kristian Eide > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com