Received: from nirvana.dataparty.no (v097b.studby.ntnu.no [129.241.151.97]) by postgresql.org (8.11.3/8.11.4) with SMTP id g1IIsZE85518 for ; Mon, 18 Feb 2002 13:54:35 -0500 (EST) (envelope-from kreide@online.no) Received: (qmail 21352 invoked from network); 18 Feb 2002 18:54:33 -0000 Received: from v107b.studby.ntnu.no (HELO speed) (129.241.151.107) by v097b.studby.ntnu.no with SMTP; 18 Feb 2002 18:54:33 -0000 Message-ID: <00bd01c1b8ad$b9d03e90$6b97f181@speed> From: "Kristian Eide" To: Subject: Why is my index not used Date: Mon, 18 Feb 2002 19:54:42 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200202/220 X-Sequence-Number: 6644 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