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 1iyzVy-0005Df-Co for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 14:49:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iyzVw-0006Gc-NB for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 14:49:20 +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 1iyzVw-0006GU-9o for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 14:49:20 +0000 Received: from zimbra.optitool.de ([82.135.4.195]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iyzVo-0003TJ-QV for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 14:49:19 +0000 Received: from zimbra.optitool.de (localhost [IPv6:::1]) by zimbra.optitool.de (Postfix) with ESMTPS id 60967447190; Tue, 4 Feb 2020 15:49:10 +0100 (CET) Received: from localhost (localhost [IPv6:::1]) by zimbra.optitool.de (Postfix) with ESMTP id 46126447179; Tue, 4 Feb 2020 15:49:10 +0100 (CET) DKIM-Filter: OpenDKIM Filter v2.10.3 zimbra.optitool.de 46126447179 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=optitool.de; s=E37AA3DA-415C-11E8-B16D-258E1F829521; t=1580827750; bh=xJNxKviea8xisJ2YDCeoysBey/m+IHDerg9pTe9K8Tk=; h=Date:From:To:Message-ID:MIME-Version; b=K+j2HwEwVIMdIy+XvwAetwj8bqpUlK7pG6PRZqwkno8wBt0xj3xGXjG1Vo09Wk/i9 BqLo6tfY+eI/Cdq0gDrdwkSQuvhmZF20HJ1P0ClybB/nTFkSQlBLbinvrXMn6T0xH8 qYzUqAL7oRxBTcNkuKQoiXNX1VO2NQfE1uW8RSCFq0c5sPktJnm2cMd2LdyS8jLBJJ h4p3NnNjFhrleVRc9ISyraRar010js91EZdIVlGBpxoJNvKERFH9PleBVsdeBTRTrZ kwUFUd7FwOSZJslHCCM66EOrExPOOC/H2+X2AXXVZr7Dojn3TFnu9I5TGf/Mm7ueoc QydXgArNLswBw== Received: from zimbra.optitool.de ([IPv6:::1]) by localhost (zimbra.optitool.de [IPv6:::1]) (amavisd-new, port 10026) with ESMTP id A93j0DLkyqTo; Tue, 4 Feb 2020 15:49:10 +0100 (CET) Received: from zimbra.optitool.de (localhost [127.0.0.1]) by zimbra.optitool.de (Postfix) with ESMTP id 29DDB447187; Tue, 4 Feb 2020 15:49:10 +0100 (CET) Date: Tue, 4 Feb 2020 15:49:10 +0100 (CET) From: Thomas Butz To: Andres Freund Cc: pgsql-bugs Message-ID: <87490169.275301.1580827750088.JavaMail.zimbra@optitool.de> In-Reply-To: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> References: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> Subject: Re: BUG #16241: Degraded hash join performance MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [2001:a60:a0c8:100:47ef:5fd3:bc93:5baf] X-Mailer: Zimbra 8.8.15_GA_3888 (ZimbraWebClient - FF72 (Linux)/8.8.15_GA_3890) Thread-Topic: BUG #16241: Degraded hash join performance Thread-Index: Suszl1eCDRD6ri40oDsyvOutlTYqwA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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. 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? > Von: "Andres Freund" > An: "Thomas Butz" , "Tom Lane" > CC: "pgsql-bugs" > Gesendet: Dienstag, 4. Februar 2020 14:44:08 > Betreff: Re: BUG #16241: Degraded hash join performance > Hi, > > > On 2020-02-04 10:02:45 +0100, Thomas Butz wrote: >> I've executed the query twice and attached the generated reports. > > Interesting! The no-children one clearly shows that a lot of the the > time is spent evaluating regular expressions (there's other regex > functions in the profile too): > > 23.36% postgres postgres [.] subcolor > > and that this is inside a stack of plpgsql functions: > > | | |--18.95%--exec_stmt_block > | | | exec_stmt > | | | plpgsql_exec_function > | | | plpgsql_call_handler > | | | ExecInterpExpr > | | | ExecEvalExpr (inlined) > | | | exec_eval_simple_expr > | | | (inlined) > [...] > | | | plpgsql_exec_function > | | | plpgsql_call_handler > | | | ExecInterpExpr > | | | ExecEvalExpr (inlined) > | | | exec_eval_simple_expr (inlined) > [...] > | | | plpgsql_exec_function > | | | plpgsql_call_handler > | | | ExecInterpExpr > | | | ExecEvalExpr (inlined) > [...] > > > I'm not aware of any relevant regular expression evaluation changes > between 11 and 12. Tom, does this trigger anything? > > Thomas, could you provide a EXPLAIN (ANALYZE, VERBOSE) for both > versions? Clearly the hash join is where the time is spent: > > -> Hash Join (cost=1.000..6436.030 rows=44 width=517) (actual > time=5.532..3383.213 rows=2779 loops=1) > Buffers: shared hit=9077 > -> Index Scan using planet_osm_hstore_line_way_idx on planet_osm_hstore_line > planet_osm_hstore_line (cost=0.550..6419.380 rows=491 width=352) (actual > time=0.423..19.147 rows=2779 loops=1) > Index Cond: (planet_osm_hstore_line.way && > '01030000A0110F00000100000005000000AF78D2F4ECA632411CD92E58CD2E58410000000000000000AF78D2F4ECA63241E4EED5138D3958410000000000000000D3CF6EE3EBD13241E4EED5138D3958410000000000000000D3CF6EE3EBD132411CD92E58CD2E58410000000000000000AF78D2F4ECA632411CD92E58CD2E58410000000000000000'::geometry) > Filter: ((((planet_osm_hstore_line.tags -> 'name'::text) IS NOT NULL) OR > ((planet_osm_hstore_line.tags -> 'oneway'::text) = ANY ('{yes,-1}'::text[])) OR > ((planet_osm_hstore_line.tags -> 'junction'::text) = 'roundabout'::text)) AND > ((planet_osm_hstore_line.tags -> 'highway'::text) = ANY > ('{motorway,motorway_link,trunk,trunk_link,primary,primary_link,secondary,secondary_link,tertiary,tertiary_link,residential,unclassified,road,service,pedestrian,raceway,living_street,construction}'::text[]))) > Buffers: shared hit=9077 > -> Hash (cost=0.230..0.230 rows=18 width=32) (actual time=0.029..0.030 rows=18 > loops=1) > -> Values Scan (cost=0.000..0.230 rows=18 width=32) (actual time=0.003..0.017 > rows=18 loops=1) > > but I don't think it's the hash condition itself - there's no plpgsql > functions necessary for the join as far as I can tell. A VERBOSE EXPLAIN > might help up with knowing what the function being called actually is. > > It seems quite possible that all that time is spent inside plpgsql > functions that are part of postgis. And that the postgis version > difference between 2.5 and 3.0 is the relevant part here, not the > postgres version difference. Or it's just in the application code: > > The sort step shows among others the following column to be sorted on: > osml10n_get_streetname_from_tags(planet_osm_hstore_line.tags, true, false, ' - > '::text, 'de'::text, planet_osm_hstore_line.way, NULL::text)) DESC > which is actually likely computed as the output of the HashJoin > node. And is the only obvious candidate for a plpgsql function > potentially taking a lot of the time. > > Is that function defined exactly the same between the version? Is the > underlying dataset roughly the same size? I suggest running the query > without it, and checking how long it takes. > > Regards, > > Andres -- Thomas Butz