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 1iZEOn-000457-33 for pgsql-bugs@arkaria.postgresql.org; Mon, 25 Nov 2019 13:27:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iZEOl-0005OQ-EA for pgsql-bugs@arkaria.postgresql.org; Mon, 25 Nov 2019 13:27:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iZEOl-0005Jp-3v for pgsql-bugs@lists.postgresql.org; Mon, 25 Nov 2019 13:27:27 +0000 Received: from mail-wr1-x441.google.com ([2a00:1450:4864:20::441]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iZEOd-0001uS-5l for pgsql-bugs@lists.postgresql.org; Mon, 25 Nov 2019 13:27:26 +0000 Received: by mail-wr1-x441.google.com with SMTP id 4so14743857wro.7 for ; Mon, 25 Nov 2019 05:27:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:subject:message-id:references:mime-version :content-disposition:content-transfer-encoding:in-reply-to; bh=pbuwvVzZgTqwVWamnbu5f7riXqVYWzyi614XXbsWEIY=; b=nAQZ2beUuk2/GZVU7oWOawnWqLqlPTWGE5FR785hj4Z15O+r82AXWZ7pNGK/or6Jnl UJ6p2KFZiCzVxTOStQVpewbyKTf0x1GqP6aQG39qxfXq5cBYnqsn5cMTTlUD5ZKPeQmE qOxAOS+UCwtUufZQPDM9wXk7UMfbYjtfZJG1mgoLwuKbZSoq19IHxRHIvyfzr+6FoCGk 5yTKRZ5B/g7QZCU4z5I8WMymergiLuclo96KPhec9VsqPxvjUy/yATmFhO8IgbQBOtr6 v0B4Sg+gNFw2K9GqOsS0PGyCfeZIqoE++UEJvtFPK1eBo1mIET5Y2Q9t4UeVwZLghrKT tFcQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:references :mime-version:content-disposition:content-transfer-encoding :in-reply-to; bh=pbuwvVzZgTqwVWamnbu5f7riXqVYWzyi614XXbsWEIY=; b=HEuaB6ZF/Jr0l6nvJXyEUqdnG0Pfb6br+w9yqLOM/jLFXUdZe9ARIiBRYcB0GUa8HN uZ9uwAJlwuaZVsyWO/WRLox4DUSShAsM+nWIOU4FMjaeFjBIyZAglEDl5THPvCRO2uQN 2uCY86qAZveb8jSByR37tf2NVPTcrI8iZfDMd/k3S3xhLLGx2om+Q3DFhVi2UliykSrz bzCGwB/wTh5gUI7EviqKPZHCifxNQWzXpcd5jy2WSMjPJoNVTZSY6QYKh4I9jLIAQEG0 +ekOwhh36Fv48F8LmD5TAb1NVB9yRBlyvB6FiRHOavtAXeWd9Ec0y7GGbK16KEd0yRNS HFOw== X-Gm-Message-State: APjAAAVO/NnzRW7Nt0lZ9Kf071HZU4zbBz2ohTHCL6LTOiepSxtdVrVG 7QtoMg6P8zOCTjUf9bJ8lhbj/g== X-Google-Smtp-Source: APXvYqxK99CajWc+KLRCmjGS2FS+XrKdt9qkd8bEjqo62/VuzGZCqzrOsgMtO4nlkLU7uPEbLO0ERA== X-Received: by 2002:adf:c649:: with SMTP id u9mr7696729wrg.20.1574688438071; Mon, 25 Nov 2019 05:27:18 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id m3sm11133200wrb.67.2019.11.25.05.27.17 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 25 Nov 2019 05:27:17 -0800 (PST) Date: Mon, 25 Nov 2019 14:27:15 +0100 From: Tomas Vondra To: alexfilus@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16136: Bug with cost calculation and JIT application Message-ID: <20191125132715.qrbeytjrmhinba7m@development> References: <16136-34535de58a0ff493@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <16136-34535de58a0ff493@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Mon, Nov 25, 2019 at 01:00:34PM +0000, PG Bug reporting form wrote: >The following bug has been logged on the website: > >Bug reference: 16136 >Logged by: Александр Филиппенко >Email address: alexfilus@gmail.com >PostgreSQL version: 12.1 >Operating system: Ubuntu 18.04 >Description: > >I had very strange behaviour of optimizator. >https://explain.depesz.com/s/GcHL >https://explain.depesz.com/s/sTyG >Both explains are for same query and same data set, but in second case I >turn off JIT. I guess it's because of wrong cost calculation like this >Nested Loop (cost=463.86..5575122.76 rows=652 width=74) (actual >time=9.685..67.713 rows=18 loops=1) > Not sure what exactly you mean by "wrong cost calculation"? The reason for the long duration with JIT seems pretty clear: Functions: 237 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 45.190 ms, Inlining 37.452 ms, Optimization 1292.680 ms, Emission 1076.752 ms, Total 2452.074 ms That is, it took about 2.3s to optimize and emit the JIT code, most likely because due to a fairly high number of functions. I'm not sure that's something we can really fix, but there were discussions about being smarter about optimizations etc. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services