Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A4E259FA540 for ; Tue, 28 Aug 2007 18:06:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 60485-02 for ; Tue, 28 Aug 2007 18:06:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8B5689FA48B for ; Tue, 28 Aug 2007 18:06:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7SL6CUY003929; Tue, 28 Aug 2007 17:06:12 -0400 (EDT) To: Pallav Kalva cc: pgsql-performance@postgresql.org Subject: Re: 8.2.4 Chooses Bad Query Plan In-reply-to: <46D4777B.5030701@livedatagroup.com> References: <46D43A8C.2080201@livedatagroup.com> <1206.1188326173@sss.pgh.pa.us> <46D4777B.5030701@livedatagroup.com> Comments: In-reply-to Pallav Kalva message dated "Tue, 28 Aug 2007 15:28:59 -0400" Date: Tue, 28 Aug 2007 17:06:12 -0400 Message-ID: <3926.1188335172@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/368 X-Sequence-Number: 26498 Pallav Kalva writes: > I have analyzed tables again and also my default_stats_target is set to > 100, still it shows the same plan. >>> -> Index Scan using idx_accountactivity_fkactivityid on >>> accountactivity accountact0_ (cost=0.00..3.94 rows=1 width=16) >>> Index Cond: (accountact0_.fkactivityid = >>> activity1_.activityid) >>> Filter: (fkaccountid = 1455437) >>> -> Index Scan using >>> idx_accountactivity_fkaccountid on accountactivity accountact0_ >>> (cost=0.00..1641.42 rows=1343 width=16) (actual time=115.348..864.416 >>> rows=10302 loops=1) >>> Index Cond: (fkaccountid = 1455437) Oh, my bad, I failed to look closely enough at these subplans. I thought they were identical but they're not using the same scan conditions, so the rowcount estimates shouldn't be comparable after all. Could you try EXPLAINing (maybe even with ANALYZE) the query *without* the LIMIT clause? I'm curious to see what it thinks the best plan is then. regards, tom lane