Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V50lU-0007wN-21 for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 21:50:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V50lT-0005X8-Ht for pgsql-performance@arkaria.postgresql.org; Thu, 01 Aug 2013 21:50:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V50lS-0005X2-PT for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 21:50:30 +0000 Received: from mail-la0-x233.google.com ([2a00:1450:4010:c03::233]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V50lQ-0000gk-B3 for pgsql-performance@postgresql.org; Thu, 01 Aug 2013 21:50:30 +0000 Received: by mail-la0-f51.google.com with SMTP id fp13so1825992lab.10 for ; Thu, 01 Aug 2013 14:50:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:cc:subject:references:date:in-reply-to:message-id :user-agent:mime-version:content-type; bh=kpVbKNCDDSXop/XzFivLNVd9DzLYDtlbnUIu5+HEYcc=; b=Uc4MNMK3tf0O+ae8EkWPgw9ecVIph62Qo+9I68BYyLW2cHDKEeg0DyHi0h5mpfVkG/ SNv3asXfzwXCw8CuzEjyfG0+Uh7GvMcyyJLc7j0q2rWJlHqt0apEzXYicP19XkrLt/bF hI+g7B8tP79sHM71DpEBpKjO0kcH15Ca1jr+SAoEXtxEmNojGjpX8UvWuox/eR0YIoBe CUWy9byYiAMbXK0bhU3MEL0tVn8ykw4Advy60VV9CaFXYtfRS+m78OmvAbvmYZ83oDid qBsNWTtEy04HkhuWssPdR0dTgssIPpXhgQ4RcPisnFSbj6Xq2eaqIwuTO9B42N5v9jlu e4Lw== X-Received: by 10.152.9.194 with SMTP id c2mr1633203lab.83.1375393826812; Thu, 01 Aug 2013 14:50:26 -0700 (PDT) Received: from home.progtech.ru (os.progtech.ru. [80.252.153.41]) by mx.google.com with ESMTPSA id v18sm2023223lbd.5.2013.08.01.14.50.25 for (version=TLSv1.2 cipher=RC4-SHA bits=128/128); Thu, 01 Aug 2013 14:50:25 -0700 (PDT) From: Sergey Burladyan To: Jeff Janes Cc: "pgsql-performance\@postgresql.org" Subject: Re: Looks like merge join planning time is too big, 55 seconds References: <8761vpwid4.fsf@seb.koffice.internal> <87li4lusvd.fsf@seb.koffice.internal> Date: Fri, 02 Aug 2013 01:50:23 +0400 In-Reply-To: (Jeff Janes's message of "Thu, 1 Aug 2013 12:51:04 -0700") Message-ID: <87li4l9j34.fsf@home.progtech.ru> 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: -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 Jeff Janes writes: > I think the next step would be to run gdb -p (but don't start > gdb until backend is in the middle of a slow explain), then: Sorry, I am lack debug symbols, so call trace is incomplete: 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 #0 0x00007ff766967620 in read () from /lib/libc.so.6 #1 0x00007ff7689cfc25 in FileRead () #2 0x00007ff7689ea2f6 in mdread () #3 0x00007ff7689cc473 in ?? () #4 0x00007ff7689ccf54 in ReadBufferExtended () #5 0x00007ff7688050ca in index_fetch_heap () #6 0x00007ff76880523e in index_getnext () #7 0x00007ff768a63306 in ?? () #8 0x00007ff768a67624 in ?? () #9 0x00007ff768a67d9c in ?? () #10 0x00007ff768a68376 in mergejoinscansel () #11 0x00007ff76896faa6 in initial_cost_mergejoin () #12 0x00007ff768977695 in ?? () #13 0x00007ff76897816c in add_paths_to_joinrel () #14 0x00007ff76897981b in make_join_rel () #15 0x00007ff768979ac9 in join_search_one_level () #16 0x00007ff76896a3ab in standard_join_search () #17 0x00007ff7689837c1 in query_planner () #18 0x00007ff768985260 in ?? () #19 0x00007ff7689870a9 in subquery_planner () #20 0x00007ff76898736e in standard_planner () #21 0x00007ff7689ef3ce in pg_plan_query () #22 0x00007ff7688c94a3 in ?? () #23 0x00007ff7688c9809 in ExplainQuery () #24 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so #25 0x00007ff7689f1f27 in ?? () #26 0x00007ff7689f3295 in ?? () #27 0x00007ff7689f388f in PortalRun () #28 0x00007ff7689ef96d in ?? () #29 0x00007ff7689f0950 in PostgresMain () #30 0x00007ff7689aa7a3 in ?? () #31 0x00007ff7689ad73c in PostmasterMain () #32 0x00007ff768948e4b in main () #0 0x00007ff766973950 in lseek64 () from /lib/libc.so.6 #1 0x00007ff7689cf88d in FileSeek () #2 0x00007ff7689ea09c in mdwrite () #3 0x00007ff7689cb12f in ?? () #4 0x00007ff7689cca43 in ?? () #5 0x00007ff7689ccf54 in ReadBufferExtended () #6 0x00007ff7688050ca in index_fetch_heap () #7 0x00007ff76880523e in index_getnext () #8 0x00007ff768a63306 in ?? () #9 0x00007ff768a67624 in ?? () #10 0x00007ff768a67d9c in ?? () #11 0x00007ff768a68376 in mergejoinscansel () #12 0x00007ff76896faa6 in initial_cost_mergejoin () #13 0x00007ff768977695 in ?? () #14 0x00007ff76897816c in add_paths_to_joinrel () #15 0x00007ff76897981b in make_join_rel () #16 0x00007ff768979ac9 in join_search_one_level () #17 0x00007ff76896a3ab in standard_join_search () #18 0x00007ff7689837c1 in query_planner () #19 0x00007ff768985260 in ?? () #20 0x00007ff7689870a9 in subquery_planner () #21 0x00007ff76898736e in standard_planner () #22 0x00007ff7689ef3ce in pg_plan_query () #23 0x00007ff7688c94a3 in ?? () #24 0x00007ff7688c9809 in ExplainQuery () #25 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so #26 0x00007ff7689f1f27 in ?? () #27 0x00007ff7689f3295 in ?? () #28 0x00007ff7689f388f in PortalRun () #29 0x00007ff7689ef96d in ?? () #30 0x00007ff7689f0950 in PostgresMain () #31 0x00007ff7689aa7a3 in ?? () #32 0x00007ff7689ad73c in PostmasterMain () #33 0x00007ff768948e4b in main () #0 0x00007ff766973950 in lseek64 () from /lib/libc.so.6 #1 0x00007ff7689cf88d in FileSeek () #2 0x00007ff7689ea2b9 in mdread () #3 0x00007ff7689cc473 in ?? () #4 0x00007ff7689ccf54 in ReadBufferExtended () #5 0x00007ff7688050ca in index_fetch_heap () #6 0x00007ff76880523e in index_getnext () #7 0x00007ff768a63306 in ?? () #8 0x00007ff768a67624 in ?? () #9 0x00007ff768a67d9c in ?? () #10 0x00007ff768a68376 in mergejoinscansel () #11 0x00007ff76896faa6 in initial_cost_mergejoin () #12 0x00007ff768977695 in ?? () #13 0x00007ff76897816c in add_paths_to_joinrel () #14 0x00007ff76897981b in make_join_rel () #15 0x00007ff768979ac9 in join_search_one_level () #16 0x00007ff76896a3ab in standard_join_search () #17 0x00007ff7689837c1 in query_planner () #18 0x00007ff768985260 in ?? () #19 0x00007ff7689870a9 in subquery_planner () #20 0x00007ff76898736e in standard_planner () #21 0x00007ff7689ef3ce in pg_plan_query () #22 0x00007ff7688c94a3 in ?? () #23 0x00007ff7688c9809 in ExplainQuery () #24 0x00007ff7648095e2 in ?? () from /usr/lib/postgresql/9.2/lib/pg_stat_statements.so #25 0x00007ff7689f1f27 in ?? () #26 0x00007ff7689f3295 in ?? () #27 0x00007ff7689f388f in PortalRun () #28 0x00007ff7689ef96d in ?? () #29 0x00007ff7689f0950 in PostgresMain () #30 0x00007ff7689aa7a3 in ?? () #31 0x00007ff7689ad73c in PostmasterMain () #32 0x00007ff768948e4b in main () -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance