Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 19DC413C6999 for ; Fri, 4 May 2012 11:43:34 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SQJjJ-0002sc-4H for pgsql-performance@postgresql.org; Fri, 04 May 2012 14:43:33 +0000 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 q44EhJR7005026; Fri, 4 May 2012 10:43:19 -0400 (EDT) To: Dan Fairs cc: pgsql-performance@postgresql.org Subject: Re: Unexpected sequence scan In-reply-to: <07F01B2A-648A-4BA7-B056-EC4EA2FFE0D0@gmail.com> References: <07F01B2A-648A-4BA7-B056-EC4EA2FFE0D0@gmail.com> Comments: In-reply-to Dan Fairs message dated "Fri, 04 May 2012 15:24:33 +0100" Date: Fri, 04 May 2012 10:43:19 -0400 Message-ID: <5025.1336142599@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/48 X-Sequence-Number: 46872 Dan Fairs writes: > I have a query which is running slowly, and the query plan shows an unexpected sequence scan where I'd have expected the planner to use an index. Setting enable_seqscan=off causes the planner to use the index as expected. That hashjoin plan doesn't look at all unreasonable to me. The fact that it actually comes out a lot slower than the nestloop with inner indexscan suggests that you must be running with the large table completely cached in RAM. If that's the normal state of affairs for your database, you should consider decreasing the random_page_cost setting so that the planner will plan appropriately. regards, tom lane