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 1v3K8U-00EVR9-G8 for pgsql-performance@arkaria.postgresql.org; Mon, 29 Sep 2025 20:06:14 +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 1v3K8S-003o4t-4w for pgsql-performance@arkaria.postgresql.org; Mon, 29 Sep 2025 20:06:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v3K8R-003o4l-NQ for pgsql-performance@lists.postgresql.org; Mon, 29 Sep 2025 20:06:12 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v3K8N-000ltM-22 for Pgsql-performance@postgresql.org; Mon, 29 Sep 2025 20:06:11 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-b3b27b50090so429912066b.0 for ; Mon, 29 Sep 2025 13:06:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1759176364; x=1759781164; darn=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=o31hNeprcNFz76pylAmgZTnmxJ1EOSmk4dx6gO8DtqU=; b=cSyo40BC14FNR7iWORaupir/+OhpDhh/5pYKKZwrTaajOkBM2yJ3Cjv2uflF30X/MK OuOrdMaIb/LE4miei/QMq0SR7eCtzVfzX2GQPIs1vvI+/2jiN5p4HJYN/swLRQqlNcTf LkSulo6jQA7Izf1gD3W7laO78urSK2MUbnQSWauCby+EF4kyIGg1rGSQJbd68CNmQJF+ lysjeGNLiqK5VZwPRMMZLHmUZkXx+jhUxAO/OAZFmAj1FWNIJUpPq8C6LULURHp06DcI ZB196gBGoudbASQ0sex2fSPt4vP7qcCiaWkTLW+j30BKGvaaIAMiVYq2mlNfGl+RZFM3 E/fw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759176364; x=1759781164; 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=o31hNeprcNFz76pylAmgZTnmxJ1EOSmk4dx6gO8DtqU=; b=g1BYgJdoVfLvn7ahEI5UVhL4vVu5YV6Fhs0IyPt7n5G3WLC6Qlk4s/QrxbLKliP17X Xh4+laqlqOhmgVSuJLztyfpHGQ3gzNyLnfhnkjy3CjS6FfchVE80+QmdK2/XQATakzrh RzYo8Xf4eGcL3w9W7ZH8Y4uMXmV3F8HazK4Bizl4L34N6LDmZ6T6wFx5nz1Sm4BICk7J xzmSmNHuzKZrDWDCUG8m+ekqm4+6EY1bHX7UsL6rBVJvUmwzvEmJoJPqEiandegvIxBI L1nLo0Z5YXVouYkXdSrCGQStUl7zeISXA33ZLKiRdO4JdkvIj1dtaHw0rVZyYHKrDdQU 6CbQ== X-Forwarded-Encrypted: i=1; AJvYcCVm8W8ojAKgF4ydKfmvFPBf6TzQPr3L/vodP3mvfMnudjRf/mk7xA7WRNkl/1geJPrlbVcUKOs8bJysf23wfPYjDg==@postgresql.org X-Gm-Message-State: AOJu0YxPm5X3Y07elfOsK/oeSe4Ef9lx4YaRZsHr4H7KCpp7bkdgQv9S 4I4jPOkRnaisra60IeiolLu8pkQVOv0IC27SjFPO+EUCP6UyUzKfXq9ZDr7CncFzIAJp8bMRiRW uX1/TrMpC5vWu3MSGQqQQyifejPKUxM0= X-Gm-Gg: ASbGnctbC968ba/x/jT4O0kDSdwVKKG70gEQj8SAshfzROsNgvwh6P3LP/93+71JbbJ /xpQWe9KvggbhRsB3mzk+EGq0Yoco4GdCJu2zAVm2dQ2/YisFW3QiBtiVOyjRsuLNRo0TLK4Swd pPRWsrb5cGX26H5cJ3emzfyuQec+3Xe7BHxIDCqA42pkkV+F6ZEbZWJSqL5Ocst552V4d71V9H5 77OSA== X-Google-Smtp-Source: AGHT+IFZtdsLzUwuMh//VNA4b0Qeh8yVEAI7f27oaE52eZnts6oc6CIyesAupOGQny8z5DQdbFDc6jtNxZ3iGgRqJyw= X-Received: by 2002:a17:906:d542:b0:b41:79ff:250c with SMTP id a640c23a62f3a-b4179ff27d6mr151860066b.23.1759176364314; Mon, 29 Sep 2025 13:06:04 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Chetan Date: Mon, 29 Sep 2025 21:05:27 +0100 X-Gm-Features: AS18NWAuUM3ZT00FT5mOvUZdh54JC-aPt7X_MZZ--8FDEfGn8dpDIfW-iqZhUHE Message-ID: Subject: Re: Partition pruning is not happening (even in PG18) To: Lauro Ojeda Cc: David Rowley , Pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="0000000000004ad305063ff62a12" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004ad305063ff62a12 Content-Type: text/plain; charset="UTF-8" On Mon, 29 Sept 2025 at 17:55, Lauro Ojeda wrote: > Hi David, > Thank you for your nice reply. > > I have the impression there is something heavily penalizing the usage of > partition pruning. While trying to go a bit further, I realized that > partition pruning is not occurring because the planner gives the cost of > seq-scanning all partitions to just over 2M units, but the query takes over > 3 minutes. When forcing the usage of PK index via hinting (which down the > line forces the partition pruning to happen), the cost sky-rocket to 53M > units (however scanning 4 partitions out of 12). Even though with much > higher cost, the query launches less workers and completes in a third (or > less) of the time of all partition scans. > > My observation is that the cost of seq-scanning each partition is > estimated in ~124k units, but index-scanning each partition is 440k (say, > 3x higher). Therefore, scanning 4 partitions, the total cost should be > ~1.7M units, which is below the 2M units from the cost of all partition > scanning, thus the planner should prefer the indexed path instead. However > when analyzing the hinted query (doing index scan and thus partition > pruning) we can note that the cost estimated to each partition scan was > added to the final cost, even though 8 out of 12 partitions were not > scanned (never executed). > > postgres=> explain analyze > /*+ IndexScan(accounts accounts_pkey) */ > select aid, abalance > from accounts > where transaction_date in (select dt_col from t2); > > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=1.56..53359847.63 rows=2767123 width=8) (actual > time=2.299..111346.649 rows=1664742 loops=1) > -> HashAggregate (cost=1.12..1.23 rows=10 width=4) (actual > time=1.060..40.562 rows=6 loops=1) > Group Key: t2.dt_col > Batches: 1 Memory Usage: 24kB > -> Seq Scan on t2 (cost=0.00..1.10 rows=10 width=4) (actual > time=1.048..1.050 rows=6 loops=1) > -> Append (cost=0.43..5302777.61 rows=3320703 width=12) (actual > time=1.554..18514.181 rows=277457 loops=6) > -> Index Scan using accounts_p1_pkey on accounts_p1 accounts_1 > (cost=0.43..434635.82 rows=273011 width=12) (actual > time=2.214..17627.113 rows=276971 loops=1) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p2_pkey on accounts_p2 accounts_2 > (cost=0.43..441097.44 rows=277283 width=12) (actual > time=2.185..16280.020 rows=276634 loops=1) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p3_pkey on accounts_p3 accounts_3 > (cost=0.43..441666.94 rows=277392 width=12) (actual > time=0.751..25992.220 rows=278390 loops=1) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p4_pkey on accounts_p4 accounts_4 > (cost=0.43..441689.70 rows=277483 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p5_pkey on accounts_p5 accounts_5 > (cost=0.43..441883.26 rows=277546 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p6_pkey on accounts_p6 accounts_6 > (cost=0.43..*441857.26 *rows=277591 width=12) (actual > time=1.376..17001.777 rows=277582 loops=3) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p7_pkey on accounts_p7 accounts_7 > (cost=0.43..441837.57 rows=277503 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p8_pkey on accounts_p8 accounts_8 > (cost=0.43..441843.95 rows=277511 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p9_pkey on accounts_p9 accounts_9 > (cost=0.43..441711.03 rows=277506 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p10_pkey on accounts_p10 > accounts_10 (cost=0.43..441918.96 rows=277554 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p11_pkey on accounts_p11 > accounts_11 (cost=0.43..441501.86 rows=277377 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > -> Index Scan using accounts_p12_pkey on accounts_p12 > accounts_12 (cost=0.43..434530.31 rows=272946 width=12) (never executed) > Index Cond: (transaction_date = t2.dt_col) > Planning Time: 0.879 ms > Execution Time: 111432.755 ms > (32 rows) > > By looking into it, I have the impression there is a bug in the costing > sum in that situation, where the cost of the "never executed" partitions > should be deducted from the final cost estimation, which would make pruning > to be the preferred option in this case. > > The plan generation stage is where the costing sum is figured out whereas the "never executed" marking/detection is in the execution stage. So this doesnt look to be a costing bug for the above scenario where the planner was unable to prune partitions early. > Are my assumptions correct? > Is there anything I could do to influence the planner to dismiss the cost > of "never executed" scans? > > Kind regards, > Lauro Ojeda > > > On Thu, 25 Sept 2025 at 22:18, David Rowley wrote: > >> On Fri, 26 Sept 2025 at 07:49, Lauro Ojeda wrote: >> > The only way I found to make pruning work is to force index_scan using >> pg_hint_plan, but I wanted to influence the planner to decide it by itself >> rather than relying on hints. What's the reason for this misbehaving and >> what could I do to overcome it? >> >> > Partition key: RANGE (transaction_date) >> >> > postgres=> explain analyze >> > select aid, abalance >> > from accounts >> > where transaction_date in (select dt_col from t2); >> >> The only partition pruning that exists in PostgreSQL that can prune >> for that query is for parameterised Nested Loop joins. For Hash Join, >> it's been talked about, but this requires running the partition >> pruning code for every values that goes into the Hash Table and only >> scanning the unioned set of those partitions during the hash probe >> phase. The trouble with that is that it's very hard to know in advance >> if it'll be worth the extra effort. Putting a tuple into a hash table >> is quite cheap. Running the pruning code for a range partitioned table >> is likely to be a few times more costly than the hash insert >> (depending on how many partitions there are), so if the end result is >> that nothing was pruned, then that's quite a bit of extra effort for >> no gain. >> >> What we maybe could do better is reduce the cost of the Append scan >> when there's a run-time pruning object attached. This is a little >> tricky as we currently only build that object when creating the final >> plan. To include that in the costs we'd need to move that to the Path >> generation phase so that we didn't accidentally reject Paths which >> could be cheaper than we first think. >> >> > Also, how could I contribute to get this partition pruning to work? >> >> The pgsql-hackers mailing list is where all the discussions about that >> happen. There is plenty of past discussions on these topics. One such >> (fairly) recent discussion is in [1]. There are plenty more, including >> some ideas from Robert Haas about how we might cost run-time partition >> pruning. That was likely around 2017-2018 range, so you might need to >> dig deep to find that. >> >> David >> >> [1] >> https://www.postgresql.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9bOcA%40mail.gmail.com#45314d3d01ef8ad1eebe72111989062c >> > > > -- > *Lauro Ojeda* > -- -- Regards, Chetan --0000000000004ad305063ff62a12 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, 29 Sept 2025 at 17:= 55, Lauro Ojeda <lauro.ojeda@gm= ail.com> wrote:
Hi David,
Thank you for your nice reply.
<= div>
I have the impression there is something heavily penaliz= ing the usage of partition pruning. While trying to go a bit further, I rea= lized that partition pruning is not occurring=C2=A0because the planner give= s the cost of seq-scanning all partitions to just over 2M units, but the qu= ery takes over 3 minutes. When forcing the usage of PK index via hinting (w= hich down the line forces the partition pruning to happen), the cost sky-ro= cket to 53M units=C2=A0(however scanning 4 partitions out of 12).=C2=A0Even= though with much higher cost, the query launches less workers and complete= s in a third (or less) of the time of all partition scans.

My= observation is that the cost of seq-scanning each partition is estimated i= n ~124k units, but index-scanning each partition is 440k (say, 3x higher). = Therefore, scanning 4 partitions, the total cost should be ~1.7M units, whi= ch is below the 2M units from the cost of all partition scanning, thus the = planner should prefer the indexed path instead. However when analyzing the = hinted=C2=A0query (doing index scan and thus partition pruning) we can note= that the cost estimated to each partition scan was added to the final cost= , even though 8 out of 12 partitions were not scanned (never executed).

postgres=3D> explain analyze
/*+ = IndexScan(accounts accounts_pkey) */
select aid, abalance
from accoun= ts
where transaction_date in (select dt_col from t2);
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
----------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---
=C2=A0Nested Loop =C2=A0(cost=3D1.56..533598= 47.63 rows=3D2767123 width=3D8) (actual time=3D2.299..111346.649 row= s=3D1664742 loops=3D1)
=C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cos= t=3D1.12..1.23 rows=3D10 width=3D4) (actual time=3D1.060..40.562 rows=3D6 l= oops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Group Key: t2.dt_col
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Batches: 1 =C2=A0Memory Usage: 24kB
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on t2 =C2=A0(cost=3D0.0= 0..1.10 rows=3D10 width=3D4) (actual time=3D1.048..1.050 rows=3D6 loops=3D1= )
=C2=A0 =C2=A0-> =C2=A0Append =C2=A0(cost=3D0.43..5302777.61 rows=3D= 3320703 width=3D12) (actual time=3D1.554..18514.181 rows=3D277457 loops=3D6= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using account= s_p1_pkey on accounts_p1 accounts_1 =C2=A0(cost=3D0.43..434635.82 rows=3D273011 width=3D12) (actual time=3D2.214..17627= .113 rows=3D276971 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p2_pkey on accoun= ts_p2 accounts_2 =C2=A0(cost=3D0.43..441097.44 rows=3D277283 width=3D12) (actual time=3D2.185..16280.020 rows=3D276634 = loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index = Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan using accounts_p3_pkey on accounts_p3 accounts_3 = =C2=A0(cost=3D0.43..441666.94 rows=3D277392 = width=3D12) (actual time=3D0.751..25992.220 rows=3D278390 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transactio= n_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Inde= x Scan using accounts_p4_pkey on accounts_p4 accounts_4 =C2=A0(cost=3D0.43.= .441689.70 rows=3D277483 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.= dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using a= ccounts_p5_pkey on accounts_p5 accounts_5 =C2=A0(cost=3D0.43..441883.26 row= s=3D277546 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p6_pke= y on accounts_p6 accounts_6 =C2=A0(cost=3D0.43..= 441857.26 rows=3D277591 width=3D12) (actual time=3D1.376..17001.= 777 rows=3D277582 loops=3D3)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p7_pkey on accoun= ts_p7 accounts_7 =C2=A0(cost=3D0.43..441837.57 rows=3D277503 width=3D12) (n= ever executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0In= dex Cond: (transaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Index Scan using accounts_p8_pkey on accounts_p8 accounts= _8 =C2=A0(cost=3D0.43..441843.95 rows=3D277511 width=3D12) (never executed)=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (tra= nsaction_date =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2= =A0Index Scan using accounts_p9_pkey on accounts_p9 accounts_9 =C2=A0(cost= =3D0.43..441711.03 rows=3D277506 width=3D12) (never executed)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date = =3D t2.dt_col)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan = using accounts_p10_pkey on accounts_p10 accounts_10 =C2=A0(cost=3D0.43..441= 918.96 rows=3D277554 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_c= ol)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accou= nts_p11_pkey on accounts_p11 accounts_11 =C2=A0(cost=3D0.43..441501.86 rows= =3D277377 width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using accounts_p12_pk= ey on accounts_p12 accounts_12 =C2=A0(cost=3D0.43..434530.31 rows=3D272946 = width=3D12) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: (transaction_date =3D t2.dt_col)
=C2=A0Planning= Time: 0.879 ms
=C2=A0Execution Time: 111432.755 ms
(32 rows)<= br>
By looking into it, I have the impression there is a bug = in the costing sum in that situation, where the cost of the "never exe= cuted" partitions should be deducted from the final cost estimation, w= hich would make pruning to be the preferred option in this case.
=

The plan generation stage = is where the costing sum is figured out=C2=A0 whereas the "never execu= ted"=C2=A0 marking/detection is in the execution stage.
= So this doesnt look to be a costing bug for the above scenario where the pl= anner=C2=A0was unable to prune partitions early.
=C2=A0
<= div>Are my assumptions correct?=C2=A0
Is there anything I could do to in= fluence the planner to dismiss the cost of "never executed" scans= ?

Kind regards,
Lauro Ojeda
= =C2=A0=C2=A0

On Thu, 25 Sept 2025 at 22:18, David Rowley <dgrowleyml@gmail.com= > wrote:
On F= ri, 26 Sept 2025 at 07:49, Lauro Ojeda <lauro.ojeda@gmail.com> wrote:
> The only way I found to make pruning work is to force index_scan using= pg_hint_plan, but I wanted to influence the planner to decide it by itself= rather than relying on hints. What's the reason for this misbehaving a= nd what could I do to overcome it?

> Partition key: RANGE (transaction_date)

> postgres=3D> explain analyze
> select aid, abalance
> from accounts
> where transaction_date in (select dt_col from t2);

The only partition pruning that exists in PostgreSQL that can prune
for that query is for parameterised Nested Loop joins.=C2=A0 For Hash Join,=
it's been talked about, but this requires running the partition
pruning code for every values that goes into the Hash Table and only
scanning the unioned set of those partitions during the hash probe
phase. The trouble with that is that it's very hard to know in advance<= br> if it'll be worth the extra effort. Putting a tuple into a hash table is quite cheap. Running the pruning code for a range partitioned table
is likely to be a few times more costly than the hash insert
(depending on how many partitions there are), so if the end result is
that nothing was pruned, then that's quite a bit of extra effort for no gain.

What we maybe could do better is reduce the cost of the Append scan
when there's a run-time pruning object attached. This is a little
tricky as we currently only build that object when creating the final
plan.=C2=A0 To include that in the costs we'd need to move that to the = Path
generation phase so that we didn't accidentally reject Paths which
could be cheaper than we first think.

> Also, how could I contribute to get this partition pruning to work?
The pgsql-hackers mailing list is where all the discussions about that
happen. There is plenty of past discussions on these topics. One such
(fairly) recent discussion is in [1]. There are plenty more, including
some ideas from Robert Haas about how we might cost run-time partition
pruning. That was likely around 2017-2018 range, so you might need to
dig deep to find that.

David

[1] https://www.postgresq= l.org/message-id/flat/CAApHDvoC7n_oceb%3D8z%2BMY8sTgH4xa%2ByAwBxZ4Dxv8pwkT9= bOcA%40mail.gmail.com#45314d3d01ef8ad1eebe72111989062c


--
Lauro Ojeda
=


--
--
Re= gards,
Chetan



--0000000000004ad305063ff62a12--