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 CB1B09DCA58 for ; Tue, 31 Jan 2006 23:29:43 -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 34097-02 for ; Tue, 31 Jan 2006 23:29:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by postgresql.org (Postfix) with ESMTP id 4C49D9DC821 for ; Tue, 31 Jan 2006 23:29:41 -0400 (AST) Received: from [192.168.1.100] (or-67-76-146-141.sta.sprint-hsd.net [67.76.146.141]) (authenticated bits=0) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id k113JN1v020075; Tue, 31 Jan 2006 19:19:27 -0800 Message-ID: <43E02B2F.9070202@commandprompt.com> Date: Tue, 31 Jan 2006 19:29:51 -0800 From: "Joshua D. Drake" User-Agent: Thunderbird 1.5 (X11/20051201) MIME-Version: 1.0 To: James Russell CC: pgsql-performance@postgresql.org Subject: Re: Sequential scan being used despite indexes References: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com> In-Reply-To: <3a3a16930601311914w5bd7d1d0hde7b4e495e0deb27@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Tue, 31 Jan 2006 19:19:27 -0800 (PST) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.085 required=5 tests=[AWL=0.085] X-Spam-Score: 0.085 X-Spam-Level: X-Archive-Number: 200601/507 X-Sequence-Number: 16985 > > Explaining: > Hash Join (cost=337.93..1267.54 rows=180 width=35) > Hash Cond: ("outer".message_id = "inner".message_id) > -> Seq Scan on message_meta_data (cost=0.00..739.19 rows=37719 width=30) > -> Hash (cost=337.79..337.79 rows=57 width=13) > -> Nested Loop (cost=0.00..337.79 rows=57 width=13) > -> Index Scan using thread_forum_id_idx on thread > (cost=0.00..41.61 rows=13 width=4) > Index Cond: (forum_id = 6) > -> Index Scan using message_thread_id_idx on message > (cost=0.00..22.72 rows=5 width=17) > Index Cond: ("outer".thread_id = message.thread_id) > > As you can see, the message and message_meta_data tables use a Seq > Scan. The only way I can think of forcing it to use the Index Scan in > all cases would be to use two separate nested queries: The outer query > would retrieve the list of messages in the forum, and the inner query > would retrieve the list of metadata for an individual message. > Obviously I want to avoid having to do that if possible. > > Any ideas? What does explain analyze say? Joshua D. Drake > > Many thanks if you can help. > > James -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/