X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1E8649DC876 for ; Thu, 23 Feb 2006 09:01:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36149-09 for ; Thu, 23 Feb 2006 09:01:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.hive.is (scania.ipf.is [85.197.192.147]) by postgresql.org (Postfix) with SMTP id 6F0DE9DC806 for ; Thu, 23 Feb 2006 09:01:27 -0400 (AST) Received: (qmail 49485 invoked by uid 1010); 23 Feb 2006 13:01:45 -0000 Received: from 85.197.216.186 by scania.ipf.is (envelope-from , uid 1009) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1283. spamassassin: 3.1.0. perlscan: 1.25-st-qms. Clear:RC:1(85.197.216.186):SA:0(-4.1/4.0):. Processed in 1.595911 secs); 23 Feb 2006 13:01:45 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via scania.ipf.is X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(85.197.216.186):SA:0(-4.1/4.0):. Processed in 1.595911 secs Process 49460) Received: from dsl-216-186.hive.is (HELO ?192.168.1.34?) (85.197.216.186) by mx1.hive.is with SMTP; 23 Feb 2006 13:01:41 -0000 Subject: Re: Created Index is not used From: Ragnar To: Kjeld Peters Cc: pgsql-performance@postgresql.org In-Reply-To: <43FDAC27.7050305@profiling-company.de> References: <43FDAC27.7050305@profiling-company.de> Content-Type: text/plain Date: Thu, 23 Feb 2006 13:03:26 +0000 Message-Id: <1140699807.5728.42.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.142 required=5 tests=[AWL=0.142] X-Spam-Score: 0.142 X-Spam-Level: X-Archive-Number: 200602/364 X-Sequence-Number: 17350 On fim, 2006-02-23 at 13:35 +0100, Kjeld Peters wrote: > Select and update statements are quite slow on a large table with more > than 600,000 rows. The table consists of 11 columns (nothing special). > The column "id" (int8) is primary key and has a btree index on it. > > The following select statement takes nearly 500ms: > > SELECT * FROM table WHERE id = 600000; > > A prepending "EXPLAIN" to the statement reveals a seq scan: > > EXPLAIN SELECT * FROM table WHERE id = 600000; > > "Seq Scan on table (cost=0.00..15946.48 rows=2 width=74)" > " Filter: (id = 600000)" > I tried a full vacuum and a reindex, but had no effect. Why is > PostgreSQL not using the created index? try one of: SELECT * FROM table WHERE id = '600000'; SELECT * FROM table WHERE id = 600000::int8; PostgreSQL 8+ gnari