Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E20AA64FC76 for ; Sun, 23 Nov 2008 00:21:34 -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 13455-08 for ; Sun, 23 Nov 2008 00:21:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp-out.neti.ee (smtp-out.neti.ee [194.126.126.44]) by mail.postgresql.org (Postfix) with ESMTP id E4ECD64FC5C for ; Sun, 23 Nov 2008 00:21:31 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by MXR-5.estpak.ee (Postfix) with ESMTP id 67A6A1E115B; Sun, 23 Nov 2008 06:21:29 +0200 (EET) X-Virus-Scanned: Debian amavisd-new at estpak.ee Received: from smtp-out.neti.ee ([127.0.0.1]) by localhost (MXR-5.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yWdL7CPAFRbR; Sun, 23 Nov 2008 06:21:27 +0200 (EET) Received: from Relayhost2.neti.ee (Relayhost2 [88.196.174.142]) by MXR-5.estpak.ee (Postfix) with ESMTP id C1D571E10B9; Sun, 23 Nov 2008 06:21:27 +0200 (EET) X-SMTP-Auth-NETI-Businesmail: no Received: from andrusnotebook (217-159-139-24-ppp.kjj.estpak.ee [217.159.139.24]) by Relayhost2.neti.ee (Postfix) with SMTP id D42D61FCC93; Sun, 23 Nov 2008 06:21:25 +0200 (EET) Message-ID: <3D5FFE56C89B4D55956907DD7C712F42@andrusnotebook> From: "Andrus" To: "Gregory Stark" Cc: References: <87k5avz7l5.fsf@oxford.xeocode.com> In-Reply-To: <87k5avz7l5.fsf@oxford.xeocode.com> Subject: Re: seq scan over 3.3 million rows instead of single key index access Date: Sun, 23 Nov 2008 06:20:08 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 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/211 X-Sequence-Number: 31942 Gregory, > I would suggest running ANALYZE > idtellUued at some point before the problematic query. Thank you. After adding analyze all is OK. Is analyze command required in 8.3 also ? Or is it better better to specify some hint at create temp table time since I know the number of rows before running query ? Andrus. set search_path to firma2,public; CREATE TEMP TABLE idtellUued(dokumnr INT) ON COMMIT DROP; INSERT INTO idtellUued VALUES(1249228); analyze idtelluued; explain analyze select 1 from dok JOIN rid USING(dokumnr) JOIN idtellUued USING(dokumnr) "Nested Loop (cost=0.00..275.18 rows=3 width=0) (actual time=87.266..87.388 rows=8 loops=1)" " -> Nested Loop (cost=0.00..6.95 rows=1 width=8) (actual time=36.613..36.636 rows=1 loops=1)" " -> Seq Scan on idtelluued (cost=0.00..1.01 rows=1 width=4) (actual time=0.009..0.015 rows=1 loops=1)" " -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..5.93 rows=1 width=4) (actual time=36.585..36.590 rows=1 loops=1)" " Index Cond: (dok.dokumnr = "outer".dokumnr)" " -> Index Scan using rid_dokumnr_idx on rid (cost=0.00..267.23 rows=80 width=4) (actual time=50.635..50.672 rows=8 loops=1)" " Index Cond: ("outer".dokumnr = rid.dokumnr)" "Total runtime: 87.586 ms"