Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 939BB65007C for ; Sat, 22 Nov 2008 16:41:07 -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 92984-07 for ; Sat, 22 Nov 2008 16:41:03 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id B18C864FCBE for ; Sat, 22 Nov 2008 16:41:03 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id mAMKf1W2068541 for ; Sat, 22 Nov 2008 16:41:01 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id mAMKJKlp047612 for pgsql-performance@postgresql.org; Sat, 22 Nov 2008 16:19:20 -0400 (AST) (envelope-from news) From: "Andrus" X-Newsgroups: pgsql.performance Subject: seq scan over 3.3 million rows instead of single key index access Date: Sat, 22 Nov 2008 21:33:28 +0200 Organization: Hub.Org Networking Services Lines: 31 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="windows-1257"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 200811/206 X-Sequence-Number: 31937 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 ? Andrus. 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) "Hash Join (cost=7483.22..222259.77 rows=5706 width=0) (actual time=14905.981..27065.903 rows=8 loops=1)" " Hash Cond: ("outer".dokumnr = "inner".dokumnr)" " -> Seq Scan on rid (cost=0.00..198240.33 rows=3295833 width=4) (actual time=0.036..15021.641 rows=3280576 loops=1)" " -> Hash (cost=7477.87..7477.87 rows=2140 width=8) (actual time=0.114..0.114 rows=1 loops=1)" " -> Nested Loop (cost=0.00..7477.87 rows=2140 width=8) (actual time=0.076..0.099 rows=1 loops=1)" " -> Seq Scan on idtelluued (cost=0.00..31.40 rows=2140 width=4) (actual time=0.006..0.011 rows=1 loops=1)" " -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..3.47 rows=1 width=4) (actual time=0.051..0.058 rows=1 loops=1)" " Index Cond: (dok.dokumnr = "outer".dokumnr)" "Total runtime: 27066.080 ms"