Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRyIN-0000Gn-Mh for pgsql-admin@arkaria.postgresql.org; Sat, 27 Aug 2022 16:04:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oRyIL-0002iI-Ay for pgsql-admin@arkaria.postgresql.org; Sat, 27 Aug 2022 16:04:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRyIK-0002i8-V7 for pgsql-admin@lists.postgresql.org; Sat, 27 Aug 2022 16:04:25 +0000 Received: from mail-qk1-x72f.google.com ([2607:f8b0:4864:20::72f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oRyIF-000780-Mx for pgsql-admin@lists.postgresql.org; Sat, 27 Aug 2022 16:04:23 +0000 Received: by mail-qk1-x72f.google.com with SMTP id i7so3283995qka.13 for ; Sat, 27 Aug 2022 09:04:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc; bh=PAgbgwkoyq7LAVaoUoPlwdM5wFAOwdML/MeY5c4kVBY=; b=TXi+SZztR5b+9o+4w1Ddl44XgV4g9zYjJ0g9/sfU4XBrhLfPLRDRAQ9WQFDYMzfjW4 /2GJbAvHrXiTcDAw28FYzZQkqhxF/ozBsNPu0r3JqL6r+npZ4PUBexskpQaL1shEMVQZ 12MkZkssLj/0dore1EH/4hMd9+KZF4uoPw9Q6LiRvcCjrmf40pdzmhGXVRsEpkfNMmHj i0c2WthDmgd/wLGmDOGtCA6a5O1Az6A7cgm8spar2JyTY6m8LWmRIHtGgSw2/WkREfsA jHN3TO4xMoLHPVvRZERne8i3NMa1lUilEiawgZ7209gTz9FLiAdWHZuzx5cMmk9IuHaW Qvag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc; bh=PAgbgwkoyq7LAVaoUoPlwdM5wFAOwdML/MeY5c4kVBY=; b=6q/JnYxU1ktSr3qR1aIR/NxBVY91MIJeIyPy5YcZwfYMskR5/crJtLMa7z5JbDcPNU Pzd8MryM82lKgjOAJ9nll3vVZDTiU8ZgtCWqxfSzYowwDkmqAHPGEZvUfa/aZIvrBFIX IQ0KXO7DfxJmYBjf/6YA0/2VYfBUYjOoHeCxyVkNAUvOhfVAufQIX7oScg3STbAUATdi 6TMfVUk1QE+N2zqBQO7XlsVjnDvr5C4THtvfynBr3YxN2DesIHSUZPB4GdAw0DHpP5jV /bNpje+AiFrRKgssHCdhQOM/TUdJ313SdcW4DLQ4UH31oMW3cF5nh7y3xp0h27k7QP7F Apag== X-Gm-Message-State: ACgBeo2/zOXWZOJOSYetcypOmmkM+xMe8fM+zpZePu7slZzZy+PfYtV4 3yIDxn4Q+4wV3JnWay7RB9fU5AKkGF/hJ21c X-Google-Smtp-Source: AA6agR6Fv0RR46rP78X4NotrlmGNdM3DZ+nqsoSv//pUPSQov28GjTdJGAJewSi57fzu1HgULnNz1w== X-Received: by 2002:a05:620a:25c8:b0:6ae:ba71:ea7d with SMTP id y8-20020a05620a25c800b006aeba71ea7dmr3354353qko.547.1661616257739; Sat, 27 Aug 2022 09:04:17 -0700 (PDT) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id t17-20020ac86a11000000b0031f287f58b4sm1733545qtr.51.2022.08.27.09.04.17 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 27 Aug 2022 09:04:17 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------dbtwmk04ZUNw0G9YQDy7AHNG" Message-ID: Date: Sat, 27 Aug 2022 12:04:16 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.0 Subject: Re: Postgresql 14 performance Content-Language: en-US To: Jeff Janes Cc: pgsql-admin@lists.postgresql.org References: From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------dbtwmk04ZUNw0G9YQDy7AHNG Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 8/27/22 11:49, Jeff Janes wrote: > I don't think that that follows at all. Proper index usage can vastly > improve CPU-bound performance, not just Disk-bound performance. And > that it used to be faster in the recent past suggests that it is > certainly possible for it to be faster. > > I assume he is on a system/installation without a JIT provider, so > even if jit is enabled in the postgresql.conf, it won't be used. That may also be true. The plan attached by the OP doesn't have any JIT information despite him telling us that JIT is turned on. As for the proper index usage, I would need to look at the plan details, in particular, how many rows were retrieved in each index range scan step. Unfortunately, I don't have time to do that. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------dbtwmk04ZUNw0G9YQDy7AHNG Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 8/27/22 11:49, Jeff Janes wrote:
I don't think that that follows at all.  Proper index usage can vastly improve CPU-bound performance, not just Disk-bound performance. And that it used to be faster in the recent past suggests that it is certainly possible for it to be faster.

I assume he is on a system/installation without a JIT provider, so even if jit is enabled in the postgresql.conf, it won't be used.

That may also be true. The plan attached by the OP doesn't have any JIT information despite him telling us that JIT is turned on. As for the proper index usage, I would need to look at the plan details, in particular, how many rows were retrieved in each index range scan step. Unfortunately, I don't have time to do that.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------dbtwmk04ZUNw0G9YQDy7AHNG--