Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 4D6CC1FBF225 for ; Tue, 27 Dec 2011 14:13:03 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RfbWG-0007kc-DT for pgsql-performance@postgresql.org; Tue, 27 Dec 2011 18:13:03 +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 pBRIClFM012107; Tue, 27 Dec 2011 13:12:47 -0500 (EST) To: Jim Crate cc: pgsql-performance@postgresql.org Subject: Re: Subquery flattening causing sequential scan In-reply-to: <0E417F76-FE21-4FB5-8132-0C485778A041@gmail.com> References: <0E417F76-FE21-4FB5-8132-0C485778A041@gmail.com> Comments: In-reply-to Jim Crate message dated "Tue, 27 Dec 2011 12:29:14 -0500" Date: Tue, 27 Dec 2011 13:12:46 -0500 Message-ID: <12106.1325009566@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -3.2 (---) X-Archive-Number: 201112/181 X-Sequence-Number: 45980 Jim Crate writes: > My question is why does it do a seq scan when it flattens this > subquery into a JOIN? Because it thinks there will be 3783 rows out of the msg scan, which if true would make your desired nestloop join a serious loser. You need to see about getting that estimate to be off by less than three orders of magnitude. Possibly raising the stats target on emsg_messages would help. I'd also try converting the inner NOT IN into a NOT EXISTS, just to see if that makes the estimate any better. Using something newer than 9.0.2 might help too, as we fixed some outer-join estimation bugs a few months ago. regards, tom lane