Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V763o-0004tE-RY for pgsql-performance@arkaria.postgresql.org; Wed, 07 Aug 2013 15:54:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V763n-0007oI-Fr for pgsql-performance@arkaria.postgresql.org; Wed, 07 Aug 2013 15:54:03 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V763Y-0007T4-JH for pgsql-performance@postgresql.org; Wed, 07 Aug 2013 15:53:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V763W-00065I-5S for pgsql-performance@postgresql.org; Wed, 07 Aug 2013 15:53:47 +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 r77FrE0W006241; Wed, 7 Aug 2013 11:53:14 -0400 From: Tom Lane To: slapo@centrum.sk cc: =?utf-8?q?Igor_Neyman?= , =?utf-8?q?Pavel_Stehule?= , =?utf-8?q?pgsql=2Dperformance=40postgresql=2Eorg?= Subject: Re: =?utf-8?q?RE=3A_=5BPERFORM=5D_Re=3A_=5BPERFORM=5D_Sub=2Doptimal_plan_for_a_paginated_query_on_a_view_with_another_view_inside_of_it=2E?= In-reply-to: <20130807173349.E2F843A7@centrum.sk> References: <20130802154316.59167BB5@centrum.sk>, , <20130807144239.654353D3@centrum.sk>, , <20130807164250.26C21834@centrum.sk> <20130807173349.E2F843A7@centrum.sk> Comments: In-reply-to message dated "Wed, 07 Aug 2013 17:33:49 +0200" Date: Wed, 07 Aug 2013 11:53:14 -0400 Message-ID: <6240.1375890794@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) 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 writes: > "Total runtime: 9.313 ms" in pgAdmin > "Total runtime: 9.363 ms" in psql. > But timing after the query finished was 912.842 ms in psql. Well, that's the downside of increasing join_collapse_limit and from_collapse_limit: you might get a better plan, but it takes a lot longer to get it because the planner is considering many more options. If you're sufficiently desperate, you could consider rewriting the query so that its JOIN structure matches the join order that the planner chooses at the high collapse_limit settings. Then you can reduce the limits back down and it'll still find the same plan. This tends to suck from a query readability/maintainability standpoint though :-(. The prepared-query approach might offer a solution too, if the good plan isn't dependent on specific parameter values. 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