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 1pSy0M-0006rW-AO for pgsql-general@arkaria.postgresql.org; Fri, 17 Feb 2023 10:30:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSy0L-00082N-5v for pgsql-general@arkaria.postgresql.org; Fri, 17 Feb 2023 10:30:13 +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 1pSy0K-00082D-Pr for pgsql-general@lists.postgresql.org; Fri, 17 Feb 2023 10:30:12 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSy0E-0004DA-7e for pgsql-general@postgresql.org; Fri, 17 Feb 2023 10:30:12 +0000 Received: by mail-ed1-x533.google.com with SMTP id cn2so2540451edb.4 for ; Fri, 17 Feb 2023 02:30:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:subject:user-agent:mime-version :date:message-id:from:to:cc:subject:date:message-id:reply-to; bh=F+xM4VCorBZiOHwRtFskpR4IxxruUQPHrpOJ11XlG+Q=; b=o03T37/kjOahbN9xGmdT/AY0JC65ssTGxFUtGA3Q55F/WaQ0nx56zXLmsVf8+w+p3n FHLALvQVYJEcGqHsvKnYG/maffYtrBjbpGQeAoN7icq+45QlNWlqSFWeMqUb1Bh2DCDy 8/OJkWQIwXuU0IciPH4PQN/DEYeyADwT2Sf5kS/kPjxzT+GSeYAYn1Y3E2X9A+2PP5Ti Issh1UqwnboG49FLwlXD6lZWhrhgxYDVouuimmea8yN82ovrBPwhM/2I26TuGWuoSlZg cUTCJ9qeZyK64WgyIxnYpYeSx2pe3gsJ1xwCAuC7F/BEsW8EzffsWLg69Jku/SS61Zs+ ZDAw== 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:subject:user-agent:mime-version :date:message-id:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=F+xM4VCorBZiOHwRtFskpR4IxxruUQPHrpOJ11XlG+Q=; b=ENqrL2KsVFxZhJXLW8httmO7r51hHhaR3gGfMfe0+tdXNHnsBlAPEJyNi6+FOHdXVh nopL79n6hnlDesPdhVMil7Gt/7VVOhbV62ahuhFqZeROhlMUWw3ONiYLZkJ2EQfi1WWe 9yCCMY2l2uQZ2UFh0+CqbyAl3jupf+LIrcqTEtpH1UIvegjmBK+VZHNZqBWlI8eZtxYK Ir/dvQXlFOhJQwVddwTijE+X9a2ezQab8IKly4VE5IipcMUJFZS+E8rDvus5q6+12oJi yVOwPJppioZlpbIbvzjrpdkDXwsaBxbTCtJAvOf4kdzvB26DfG754lH9suf80h5hPiJE KXrQ== X-Gm-Message-State: AO0yUKV8FcOICaylRQQjdehZTVQFCAZ9UQK28FSFyXzdbjQJj+Nhjc24 fTpSaiEsqvfjVTT9Jzj1ST0= X-Google-Smtp-Source: AK7set8TLZn9lEdgbYpfEww5hfJ/9JosnoWduF9IlVwi7sNfaHHGnOZv2JSzM6tRnL0NFh9hSrA+HQ== X-Received: by 2002:a17:906:7fca:b0:878:72f7:bd99 with SMTP id r10-20020a1709067fca00b0087872f7bd99mr9381717ejs.6.1676629804955; Fri, 17 Feb 2023 02:30:04 -0800 (PST) Received: from [192.168.1.9] (BSN-95-228-180.static.siol.net. [193.95.228.180]) by smtp.gmail.com with ESMTPSA id t16-20020a1709060c5000b0087862f45a29sm1987857ejf.174.2023.02.17.02.30.04 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 17 Feb 2023 02:30:04 -0800 (PST) Content-Type: multipart/alternative; boundary="------------E02P8NQtJjyy0XViWGEtJ0kY" Message-ID: Date: Fri, 17 Feb 2023 11:30:03 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.7.2 Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise? To: "David G. Johnston" Cc: pgsql-general References: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> From: cen 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. --------------E02P8NQtJjyy0XViWGEtJ0kY Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 16/02/2023 17:07, David G. Johnston wrote: > > No, the planner may not cause execution.  While I could imagine > extending EXPLAIN to somehow retrieve and maybe even try alternative > plans that have been fully constructed today I'm not holding my breath. > > There is little reason for the project to give any real weight to > "assuming the query to be immutable".  We do want to fix the planner > to behave better if it is mis-behaving, otherwise you do have access > to cost parameters, and potentially other planner toggles if you've > truly run into an intractable problem. > > David J. > Fair on both points. I didn't know planner toggles existed, I'll play with that. --------------E02P8NQtJjyy0XViWGEtJ0kY Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 16/02/2023 17:07, David G. Johnston wrote:

No, the planner may not cause execution.  While I could imagine extending EXPLAIN to somehow retrieve and maybe even try alternative plans that have been fully constructed today I'm not holding my breath.

There is little reason for the project to give any real weight to "assuming the query to be immutable".  We do want to fix the planner to behave better if it is mis-behaving, otherwise you do have access to cost parameters, and potentially other planner toggles if you've truly run into an intractable problem.

David J.

Fair on both points. I didn't know planner toggles existed, I'll play with that.
--------------E02P8NQtJjyy0XViWGEtJ0kY--