Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4x8b-0007Nr-Fq for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 17:58:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V4x8a-0002lm-VB for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 17:58:09 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4x8Z-0002lf-Q0 for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 17:58:07 +0000 Received: from vexbert.mr-paradox.net ([2001:470:b:28:f::1]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4x8W-0006E3-Vz for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 17:58:07 +0000 Received: by vexbert.mr-paradox.net (Postfix, from userid 18391) id 45C1584755; Thu, 1 Aug 2013 13:58:03 -0400 (EDT) Date: Thu, 1 Aug 2013 10:58:03 -0700 From: David Kerr To: Sergey Burladyan Cc: pgsql-performance@postgresql.org Subject: Re: Looks like merge join planning time is too big, 55 seconds Message-ID: <20130801175803.GA37145@mr-paradox.net> References: <8761vpwid4.fsf@seb.koffice.internal> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8761vpwid4.fsf@seb.koffice.internal> User-Agent: Mutt/1.4.2.3i X-Pg-Spam-Score: -2.0 (--) 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 On Thu, Aug 01, 2013 at 07:17:27PM +0400, Sergey Burladyan wrote: - Sergey Burladyan writes: - - > # explain - > # select i.item_id, u.user_id from items i - > # left join users u on u.user_id = i.user_id - > # where item_id = 169946840; - > QUERY PLAN - > ---------------------------------------------------------------------------------------------- - > Nested Loop Left Join (cost=0.00..397.14 rows=1 width=16) - > -> Index Scan using items_item_ux on items i (cost=0.00..358.84 rows=1 width=16) - > Index Cond: (item_id = 169946840) - > -> Index Only Scan using users_user_id_pkey on users u (cost=0.00..38.30 rows=1 width=8) - > Index Cond: (user_id = i.user_id) - > - > time: 55919.910 ms - - While running this EXPLAIN backend use disk for a long time: - TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND - 21638 be/4 postgres 2.10 M/s 9.45 M/s 0.00 % 69.04 % postgres: postgres xxxxx xxx.xxx.xxx.xxx(50987) EXPLAIN - - Why it read and write to disk 10 megabytes per second for EXPLAIN query? Cannot understand what is going on here :( That sounds familiar - is it possible you're running into this? http://www.postgresql.org/message-id/20120713065122.GA45804@mr-paradox.net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance