Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V9NQg-0003J4-7W for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2013 22:51:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V9NQf-0002RJ-Np for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2013 22:51:05 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V9NQd-0002RA-EL for pgsql-performance@postgresql.org; Tue, 13 Aug 2013 22:51:03 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V9NQW-0006rZ-Ue for pgsql-performance@postgresql.org; Tue, 13 Aug 2013 22:51:02 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id r7DMooKe021535; Tue, 13 Aug 2013 18:50:50 -0400 From: Tom Lane To: Scott Marlowe cc: Vik Fearing , "pgsql-performance@postgresql.org" Subject: Re: subselect requires offset 0 for good performance. In-reply-to: References: <4522.1375400642@sss.pgh.pa.us> <51FB61C8.4090206@dalibo.com> <5204170D.4020608@dalibo.com> Comments: In-reply-to Scott Marlowe message dated "Tue, 13 Aug 2013 15:01:26 -0600" Date: Tue, 13 Aug 2013 18:50:50 -0400 Message-ID: <21534.1376434250@sss.pgh.pa.us> X-Pg-Spam-Score: -2.8 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Scott Marlowe writes: > OK I'm bumping this one last time in the hopes that someone has an > idea what to do to fix it. > Query plan: http://explain.depesz.com/s/kJ54 > This query takes 180 seconds. It loops 17391 times across the lower > index using entries from the upper index. That seems buggy to me. There isn't all that much that the planner can do with that query. There are no equality join clauses, so no possibility of a merge or hash join; the only way to implement the join is a nestloop. Things would probably be better if it left out the one join clause it's putting into the inner indexscan condition, so it could materialize the result of the inner indexscan and then do a nestloop join against the Material node. I'd expect 9.0 and up to consider that a good idea ... but looking back, I see this is 8.4, which means you're probably out of luck on getting a better plan. 8.4's nearly out of warranty anyway --- consider upgrading. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance