Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9514564FD4B for ; Sat, 22 Nov 2008 19:59:25 -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 40976-06 for ; Sat, 22 Nov 2008 19:59:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id A602E64FC5C for ; Sat, 22 Nov 2008 19:59:22 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id mAMNwg8K016661; Sat, 22 Nov 2008 18:58:42 -0500 (EST) To: Gregory Stark cc: "Andrus" , pgsql-performance@postgresql.org Subject: Re: seq scan over 3.3 million rows instead of single key index access In-reply-to: <87k5avz7l5.fsf@oxford.xeocode.com> References: <87k5avz7l5.fsf@oxford.xeocode.com> Comments: In-reply-to Gregory Stark message dated "Sat, 22 Nov 2008 23:03:50 +0000" Date: Sat, 22 Nov 2008 18:58:42 -0500 Message-ID: <16660.1227398322@sss.pgh.pa.us> From: Tom Lane 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/210 X-Sequence-Number: 31941 Gregory Stark writes: > "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. If this can still be reproduced in 8.1.15 it would be worth looking into. My first guess is that there are multiple relevant indexes on the big table and the old bugs in choose_bitmap_and() are making it mess up. > 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. No, that's a pretty likely default assumption for a never-vacuumed, never-analyzed table. Your advice is correct though. regards, tom lane