Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4udJ-00014U-A8 for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 15:17:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V4udI-00048A-JK for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 15:17:40 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4udH-000483-C6 for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 15:17:39 +0000 Received: from mail-lb0-x22c.google.com ([2a00:1450:4010:c04::22c]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V4udA-0003OC-IK for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 15:17:38 +0000 Received: by mail-lb0-f172.google.com with SMTP id o7so1629942lbv.3 for ; Thu, 01 Aug 2013 08:17:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:subject:references:date:in-reply-to:message-id:user-agent :mime-version:content-type; bh=I3qDX4VY3XDa9Oj+kwjZCxCgZoK8UKEJvQV6Xxb4JvM=; b=pjAR4nS5I+L2VxWvE42TGacNqaacVknl80Wjdv/ojTinFu2w1dmC1rpu95nLqcZex9 8dYyzR+Eq8Ohhnrg96grTPJt/bepY6eFi8b3BlzfK9AiuoEIYotUpSXUu0nQxVH1VoKb YvtYEoCTFAQ8otjIaobK2hedFm8+5En/fRbajldx2vG68L8AZ4+5m3OZakQVLY8l3sMW BSAb1s7e7nYVqAF07agBW3My8uDkuJjFeX5Rl9rsOfo8clNNJ5iz99Q1/UXutAuUCtNe VGpvgg0m1AmcIuMSEW4/v5uuoGumd//cfOaJx/cqk2Ou7MYxhppkxdOyP5ZOkNg/t4Y7 o9yw== X-Received: by 10.112.157.226 with SMTP id wp2mr1554354lbb.65.1375370249704; Thu, 01 Aug 2013 08:17:29 -0700 (PDT) Received: from seb.koffice.internal (koffice.avito.ru. [195.151.206.25]) by mx.google.com with ESMTPSA id k6sm1395885lae.9.2013.08.01.08.17.28 for (version=TLSv1.2 cipher=RC4-SHA bits=128/128); Thu, 01 Aug 2013 08:17:28 -0700 (PDT) From: Sergey Burladyan To: pgsql-performance@postgresql.org Subject: Re: Looks like merge join planning time is too big, 55 seconds References: Date: Thu, 01 Aug 2013 19:17:27 +0400 In-Reply-To: (Sergey Burladyan's message of "Thu, 1 Aug 2013 13:55:07 +0400") Message-ID: <8761vpwid4.fsf@seb.koffice.internal> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Pg-Spam-Score: -0.1 (/) 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 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 :( -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance