Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5E707650350 for ; Sat, 22 Nov 2008 19:04:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27722-01-10 for ; Sat, 22 Nov 2008 19:04:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by mail.postgresql.org (Postfix) with ESMTP id EE44265036C for ; Sat, 22 Nov 2008 19:03:59 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k40so449284ugc.7 for ; Sat, 22 Nov 2008 15:03:58 -0800 (PST) Received: by 10.210.37.11 with SMTP id k11mr2112594ebk.153.1227395037994; Sat, 22 Nov 2008 15:03:57 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com ([87.127.95.198]) by mx.google.com with ESMTPS id p10sm3558005gvf.7.2008.11.22.15.03.55 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sat, 22 Nov 2008 15:03:57 -0800 (PST) To: "Andrus" Cc: pgsql-performance@postgresql.org Subject: Re: seq scan over 3.3 million rows instead of single key index access In-Reply-To: (Andrus's message of "Sat\, 22 Nov 2008 21\:33\:28 +0200") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:PG" 97569) References: From: Gregory Stark Organization: EnterpriseDB Date: Sat, 22 Nov 2008 23:03:50 +0000 Message-ID: <87k5avz7l5.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/209 X-Sequence-Number: 31940 "Andrus" writes: > There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int. > Instead of using single key index, 8.1.4 scans over whole rid table. > Sometimes idtelluued can contain more than single row so replacing join with > equality is not possible. > > How to fix ? Firstly the current 8.1 release is 8.1.15. Any of the bugs fixed in those 11 releases might be related to this. Secondly: > CREATE TEMP TABLE idtellUued(dokumnr INT) ON COMMIT DROP; > INSERT INTO idtellUued VALUES(1249228); > explain analyze select 1 > from dok JOIN rid USING(dokumnr) > JOIN idtellUued USING(dokumnr) > > " -> Seq Scan on idtelluued (cost=0.00..31.40 rows=2140 width=4) > (actual time=0.006..0.011 rows=1 loops=1)" The planner thinks there are 2,140 rows in that temporary table so I don't believe this is from the example posted. I would suggest running ANALYZE idtellUued at some point before the problematic query. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!