Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iyziN-0005a5-Cz for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 15:02:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iyziM-0005Am-8e for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 15:02:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iyziK-0005AV-6K for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 15:02:10 +0000 Received: from [66.111.4.28] (helo=out4-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iyziH-0003Yu-4k for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 15:02:07 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id E559621B13; Tue, 4 Feb 2020 10:01:53 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Tue, 04 Feb 2020 10:01:53 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=tS0Ouy09dGaZ1/qmkYDqSPgTGyk QFuMhdSeqeo60eqE=; b=ccShbHpakgrXKnUlq23f/ziZIIbbw9U0QxFBGZJ5F3G 5bZ5NnxOxtKREK+yaIu5HLJIsTqdeuYuXJBBfLLs7fqoI8hdK//ZOJ7LLHXBp+cv OyBAyfm4AYfpRZHBalqWIBoHY0ANdt9ub7q1q3XF+ViRSlTQAPzEZl/U6qU4x2Sr wo4OwvmNCH3HXgPNDJb8BfzESSHdwh7wTdCRcxi62Pb+jvxqRRTfu26B0jwFo9o3 dG3FXTY7PvjRNXsjuTaoU/2ZqxArwwt7ri1tvo0/brYBZ0ZI464BDvg2vEEQclil 6iY8tXzwJPrRrnicHZCi/q79vASUMBerggHwn0HTMiw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=tS0Ouy 09dGaZ1/qmkYDqSPgTGykQFuMhdSeqeo60eqE=; b=Q/+L/uLlH4wpgfvaQdjKcc 2LsfVv7TstLYLaPruDZk2EKZckShDCZOOtyekMnXV3uHa+BLAvl2ijUrwtdVmMnC ePzFdRs+twVwG5nW4tRVTXV2mR1iLBZhFuGvIG3o+YDXskTTaytUnM2VI3wHQzlr 73GFJKCIVa4xDXRI+/6dx71VMXcHg0tKrfGnJNFeIawYVZJ1S/6IhLW1AbcaCucT e4glyxge09KNsh2x3RcQ+LAvVJ+SfXOyUBu4/XX/BV/iGEWQ+lRknCKdJIFxvDN4 rAD4XzxItTfZulQbELH1wF0ujs2infhDppfl/YRFyUbh58h/X2Ax475agZFZeK0g == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedugedrgeelgdejudcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvuffkfhggtggujgesthdtredttddtvdenucfhrhhomheptehnughrvghs ucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghrrgiivghlrdguvgeqnecukfhppeeije drudeitddrvddujedrvdehtdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhep mhgrihhlfhhrohhmpegrnhgurhgvshesrghnrghrrgiivghlrdguvg X-ME-Proxy: Received: from intern.anarazel.de (c-67-160-217-250.hsd1.ca.comcast.net [67.160.217.250]) by mail.messagingengine.com (Postfix) with ESMTPA id 436013280059; Tue, 4 Feb 2020 10:01:53 -0500 (EST) Date: Tue, 4 Feb 2020 07:01:52 -0800 From: Andres Freund To: Thomas Butz Cc: pgsql-bugs Subject: Re: BUG #16241: Degraded hash join performance Message-ID: <20200204150152.prebesyfo2idnyum@alap3.anarazel.de> References: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> <87490169.275301.1580827750088.JavaMail.zimbra@optitool.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87490169.275301.1580827750088.JavaMail.zimbra@optitool.de> X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.28 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2020-02-04 15:49:10 +0100, Thomas Butz wrote: > I was able to reduce it to a minimal example: > > SELECT localized_streetname > FROM planet_osm_line > WHERE "way" && ST_SetSRID('BOX3D(1222380.956336539 6339381.37785938,1233387.888409604 6350388.309932444)'::box3d, 3857) > > Your diagnosis with osml10n_get_streetname_from_tags was spot on. I'm going to report it to the author of the osml10n extension. Cool. Still worth checking whether there's a 11 vs 12 issue inside that function. > Thank you for your help! > > PS: I've created the execution plans using EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING, SUMMARY, FORMAT json) but i wasn't aware that i might have to check the "Output" section of a node to find potential performance problems. How do you find such slow outputs without using perf? It's not trivial, unfortunately :(. You can use track_functions to get more insight, but that's better for server-wide analysis, than query specific ones. auto_explain also has options to track nested queries, which might help as well. I hope we can extend EXPLAIN with more information about this kind of thing at some point. Regards, Andres