Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uEXNU-009V2Q-9L for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 17:55:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uEXNS-000KOR-C4 for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 17:55:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uEXNR-000KNg-VW for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 17:55:46 +0000 Received: from mail-vk1-xa2e.google.com ([2607:f8b0:4864:20::a2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uEXNP-001SgI-1k for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 17:55:45 +0000 Received: by mail-vk1-xa2e.google.com with SMTP id 71dfb90a1353d-52b2290e292so1874348e0c.3 for ; Mon, 12 May 2025 10:55:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747072543; x=1747677343; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=oV3CSaA3UoT0XmP9b/t9c08i4TsbfIAQTkPvnctOFJ8=; b=DjjhppvNCTpf3b/zP3zNKfNzPANoZpYxKy4RRliJIEr0kGX8n7IepFiJJSyvI9eWpL hXIxUoNp5pp4/Y/LcG9G468TNOdluw2uvSqQ15u45lPWlpt+d2Qzg5ats1/qZrLH4t5e rw2JA+2uZvUUQrg1DzQoLuHiNljpaOsj5rweS5767suFsDRqQJQXl9TobLL3b6sSdyTE vO2GQ3mGf7u39VA7DqMvHPtrharIm771YELqzK2PqUypBIcIMzkXmpJ8mB9dUN+oLYBd HkQXh8NiC/ZxfE481CBE7h3nYTZmlyksuIcNs0LVyRUfBMAD9o8JyNdr4rE7UdWQ9pXc ySIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747072543; x=1747677343; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=oV3CSaA3UoT0XmP9b/t9c08i4TsbfIAQTkPvnctOFJ8=; b=p6FXgBvu+duNvT4iCwSOul+SE4qcvwztpefSADftQE3XjyaScq10ESKUZZBR4oMbCi HWXqL6qvOd6yEOkgjcCUUFUm8XwxqLITKMwYcu+NUoj9oWMOqmYv4ARzXJMC0/nk9Nlf pELWqyKItFVPnmLVwkOEaPYkxAwT6upFyJ7hR7axHgoJZmrv+AKTnqVCmZ+GtofVgIsV +6oVn59DZhKfP0CESqb2pzWj3Ub1aic7rUV5LK0IpLjUtyA0TZ6gR/RkJkJTNIPxPk3S 9dPe0wrhKBtQeo20t1ESrF6ASTlDqiZF/xl449RfpDWE9Qq2Vr1beHmF8OTGedOlBjGX /5rA== X-Forwarded-Encrypted: i=1; AJvYcCWLDxK6mEjYPDTGx5uKePLqho1xwTyCwKEUzgpXukk8AFdQUOCd3FStrY8TAL6XOznQJC9J5LeQThceyZ7Z9soGMg==@lists.postgresql.org X-Gm-Message-State: AOJu0YwG0+p8GxHxmWeUCtkgxSHSLrot9PecXzwwmbkb6i4uJHyxMxBT sYpvkLi1tCYq5fdjbaa0frdtjmdg0SgC6zizrrgMo/5HoOssXLhDBg7ezE5aYTl4tEr3tZN7d8X D8kctZhRQ1uUL3lePhaHsf4bO65w= X-Gm-Gg: ASbGncve33Zmn/uDr/k0hxj4Zk8wLcF1vJg8JMTfDLvBboUPuFSrjDYvCEeBKVy6cji mJGUe9WRsN2R2mCMu4JGopUcNXd2f6NWc/FFvjqqRm16m/iLkuJFz1TFMniI1wzcfnjO1f6oj04 2PA446wH1G1TBTfP68Hq5V6FPGLNgK24E= X-Google-Smtp-Source: AGHT+IEcL3DNbw+AVeuZqi8W0q6f5/SxnrasWIgYEt5ve9jk5T/PRNaJBIT4vc9Qndn15m69s4UU+VRJ9tldZQdZm18= X-Received: by 2002:a05:6122:828d:b0:52c:5062:c84d with SMTP id 71dfb90a1353d-52c538121aemr8817818e0c.0.1747072543491; Mon, 12 May 2025 10:55:43 -0700 (PDT) MIME-Version: 1.0 References: <38201.1747063982@sss.pgh.pa.us> In-Reply-To: <38201.1747063982@sss.pgh.pa.us> From: Maxim Boguk Date: Mon, 12 May 2025 20:55:07 +0300 X-Gm-Features: AX0GCFsTyfiB5bu5sac9BwKIhqEzGE7SgdD-ZWiQj1szyYNzPerii3PYitabQUw Message-ID: Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) To: Tom Lane Cc: David Rowley , Andrei Lepikhov , Pgsql Performance Content-Type: multipart/alternative; boundary="0000000000005a11a40634f40680" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005a11a40634f40680 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, May 12, 2025 at 6:33=E2=80=AFPM Tom Lane wrote: > Maxim Boguk writes: > > And the problem is that the cost of a custom plan ignores the cost of > > planning itself (which is like 2x orders of magnitude worse than the co= st > > of real time partition pruning of a generic plan). > > False. The estimate is evidently pretty wrong, but it's not that > there is no consideration at all. See around line 1370 in > src/backend/utils/cache/plancache.c. > > regards, tom lane > Thank you. Reading the code - probably the lowest hanging fruit is to make 'The current multiplier of 1000 * cpu_operator_cost' configurable in the future versions. PS: it's always nice to see when my ad-hoc idea (about N*nrelations as cost planner estimate) is already implemented. --=20 Maxim Boguk Senior Postgresql DBA Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 --0000000000005a11a40634f40680 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, May 12, 2025 at 6:33=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Boguk <maxim.boguk@gmail.com> writes: > And the problem is that the cost of a custom plan ignores the cost of<= br> > planning itself (which is like 2x orders of magnitude worse than the c= ost
> of real time partition pruning of a generic plan).

False.=C2=A0 The estimate is evidently pretty wrong, but it's not that<= br> there is no consideration at all.=C2=A0 See around line 1370 in
src/backend/utils/cache/plancache.c.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane

Thank you.=
Reading the code - probably the lowest hanging fruit is = to make
'The current mul= tiplier of 1000 * cpu_operator_cost' configurable in the future version= s.

PS: it's always ni= ce to see when my ad-hoc idea (about N*nrelations as cost planner estimate)= is already implemented.


--
= Maxim Boguk
Senior Postg= resql DBA

Phone UA: +380 99 143 0000
Phone AU: +61=C2=A0 45 218 5= 678

--0000000000005a11a40634f40680--