On Mon, Nov 10, 2025 at 12:12 AM Vitalii Tymchyshyn wrote:
> Thank you so much for both clarifying and fixing it!
FWIW the problem is limited to row compares/row constructor
comparisons that are used to decide when to end the scan. Note in
particular that row compares that decide where in the ind
Thank you so much for both clarifying and fixing it!
In our case (FYI, this is from http://github.com/cdapio/cdap) a lot of
users have just a single namespace, so it effectively means scanning till
the end of the index.
We'll fix
https://github.com/cdapio/cdap/blob/develop/cdap-data-fabric/src/main
On Sun, Nov 9, 2025 at 9:44 PM Vitalii Tymchyshyn wrote:
> I am wondering about 2 things:
> 1) Does anyone know which specific change / version made it fast?
> 2) What was the proper way to do a range index scan like WHERE (a,b,c)
> between (x1,y1,z1) and (x2,y2,z2) before the improvement.
> Note
On Fri, Nov 7, 2025 at 6:16 AM Michael Christofides
wrote:
> Thank you for the incredibly helpful (and fast) replies Peter.
You're welcome.
> Nice idea. Once it sunk in, I realised I could try the explicit "AND
> boolean_field IN (true, false)" and got it down to 2 index searches:
>
> EXPLAIN (
Thank you for the incredibly helpful (and fast) replies Peter.
> Attached is its output when I run your test query. The issue here is
that skip scan thinks that there are 4 distinct skip array values that
it must use:
1. SK_BT_MINVAL
2. false
3. true
4. SK_ISNULL
This output in particul
On Thu, Nov 6, 2025 at 2:54 PM Peter Geoghegan wrote:
> That just leaves SK_ISNULL. We cannot assume that the index doesn't
> have any NULLs (unless the query uses IS NOT NULL directly).
Actually, that won't work here. Because the optimizer recognizes that
the leading boolean column isn't nullabl
On Thu, Nov 6, 2025 at 2:01 PM Michael Christofides
wrote:
> I'm trying to understand the new Index Searches field in Postgres 18 explain
> analyze output. I've put together a super simple test case (below) expecting
> a skip scan with 2 Index Searches, one for each value in the leading
> (bool
>> Thanks for the reply, but that did not seem to help.
> I tried to replicate this as follows:
> --- CUT ---
> create table request(objectid text, productid int, data jsonb); create index
> on request(objectid, productid);
> CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[],
>
"Dirschel, Steve" writes:
>> I think you would have better luck if the planner were "inlining"
>> this function, which we can see it's not since you get a Function Scan on
>> steve1 rather than the contained query.
>> I think the only thing stopping that from happening is that the function is
>>
> > Here is the function I'm having difficulties with:
> > CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[],
> > param_productid integer DEFAULT 1) RETURNS TABLE(objectid text, n
> > text, v text, vt integer) LANGUAGE sql AS $function$
> > SELECT objectid::text
> >
"Dirschel, Steve" writes:
> Here is the function I'm having difficulties with:
> CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[],
> param_productid integer DEFAULT 1)
> RETURNS TABLE(objectid text, n text, v text, vt integer)
> LANGUAGE sql
> AS $function$
> SELECT objectid
On Thu, 30 Oct 2025 at 03:57, Carlo Sganzerla wrote:
> Yes, I also found that counterintuitive. By turning geqo = off
> (join/from_collapse_limit were 14) and looking at some metrics we obtained
> from pg_stat_statements, we found that planning times of the affected queries
> (the ones which wo
> I'm not entirely sure I follow what tests you did, some of which might
> not have been shared on the list. Also, what do you mean by "better
> plans, but also faster plans"? What's the difference?
Sorry, I should have been clearer. With "better plans" I meant faster
execution times and with "fas
On 10/28/25 16:43, Carlo Sganzerla wrote:
>> Another question is whether the difference is in planning or execution.
>> I'd expect geqo=on makes planning faster and execution slower, but maybe
>> that's not true for your test. It shouldn't be difficult to verify using
>> pg_stat_statements (which t
> Another question is whether the difference is in planning or execution.
> I'd expect geqo=on makes planning faster and execution slower, but maybe
> that's not true for your test. It shouldn't be difficult to verify using
> pg_stat_statements (which tracks both plan and exec time).
We started ex
On 10/27/25 19:17, Carlo Sganzerla wrote:
>
> I assume that the reason why the hierarchical "tree join" is much faster
> is due to the dependencies among tables, so the standard join search has
> a much narrower range of possible query paths compared to the OLTP Star
> Join case. What surprised me
Great point. One of the main reasons we are using partitioning is to
quickly drop partitions containing old data so we wouldn't be implementing
foreign key constraints any way.
On Thu, Oct 23, 2025 at 10:04 PM Laurenz Albe
wrote:
> On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote:
> > On Fr
On Thu, Oct 23, 2025 at 10:14 PM Jonathan Reis
wrote:
> Can't use pg_partman (this is true?)
>
Apologies, this warrants an explanation. It turns out I was wrong to be
concerned. I was worried about pg_partman being able to partition by the
decoded value of a column, but it already handles that q
On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote:
> On Fri, 24 Oct 2025 at 09:38, Laurenz Albe wrote:
> > I recommend that you create a primary key on each partition rather than
> > having one
> > on the partitioned table.
>
> It might be worth mentioning that doing that would forego having
Thank you all for your input on this. Here is a summary of what I have
learned from you all.
Approach 1: partition on uuid_extract_timestamp(id)
Pros: No need for custom function to convert from timestamptz to uuidv7
Partitions are human-readable
Can use pg_partman
Cons: Cannot have a
On Fri, 24 Oct 2025 at 09:38, Laurenz Albe wrote:
> I recommend that you create a primary key on each partition rather than
> having one
> on the partitioned table.
It might be worth mentioning that doing that would forego having the
ability to reference the partitioned table in a foreign key
co
On Thu, 2025-10-23 at 13:11 -0700, Jonathan Reis wrote:
> Thank you very much for your recommendations and your sample code. I
> originally had it your way, but then I found out this is not possible
>
> create table message (
> id uuid PRIMARY KEY
> -- ... plus other columns
> ) partition by
Greg,
Thank you very much for your recommendations and your sample code. I
originally had it your way, but then I found out this is not possible
create table message (
id uuid PRIMARY KEY
-- ... plus other columns
) partition by range (uuid_extract_timestamp(id));
whereas, this is
create ta
I think from a practical standpoint, partitioning directly on uuidv7 is
going to cause problems. You can't directly see the partition constraints,
you have to do tricks like your floor function to make it work, and you
have to be super careful in how you construct your where clauses. However,
what
I don't know if it will necessarily be of much use in partition pruning,
but it should work fairly well as a choice of clustered primary key
together with block range indexes.
On Wed, Oct 22, 2025 at 12:53 PM Jonathan Reis
wrote:
> Hello PostgreSQL performance team,
>
> I’m evaluating the new UU
On Wed, 22 Oct 2025 at 23:53, Jonathan Reis wrote:
> Will the planner efficiently prune partitions when queries filter by UUIDv7
> ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND
> uuidv7_floor(timestamp2) that align with time periods?
It depends. What are timestamp1 and timestamp2?
On Tue, 30 Sept 2025 at 02:49, Lauro Ojeda wrote:
> 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 prefer
On 22/9/2025 18:09, Frédéric Yhuel wrote:
On 9/22/25 15:57, Andrei Lepikhov wrote:
I wonder if we could devise another kind of extended statistic that
would provide these "partitioned statistics" without actually partitioning.I'm not sure I fully understand your case, but SQL Server demonstrates
On 10/10/2025 10:51, Frits Jalvingh wrote:
Hi Andrei,
Thanks a lot for your response. I do not fully get it though because the
well-performing query also references the outer (external?) relation:
-> where eenheid.id_h_eenheid = eenheid_s.id_h_eenheid
But your response made me think a but more.
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 2
Andrei Lepikhov writes:
> On 25/9/2025 12:41, Frédéric Yhuel wrote:
>> So, on SQL Server, you can do this:
>> CREATE STATISTICS FooStats ON foo (ackid, crit) WHERE crit = 'WARNING';
> Nice! Thanks for the report. I think the only reason why Postgres
> doesn't have it yet is the computational cos
On 25/9/2025 12:41, Frédéric Yhuel wrote:
So, on SQL Server, you can do this:
CREATE STATISTICS FooStats ON foo (ackid, crit) WHERE crit = 'WARNING';
It would be great to have a similar feature in PostgreSQL.Nice! Thanks for the report. I think the only reason why Postgres
doesn't have it yet i
On Fri, Feb 7, 2025 at 2:05 AM Jon Emord wrote:
> My expectation is that the following two queries would have roughly the same
> performance.
> They both use the same index only scans and return the same 100 rows of data.
> The main difference I see in the explain output is that the row wise
> c
On 9/10/2025 10:52, Frits Jalvingh wrote:
I do not understand why the simpler query (without the self join)
produces a plan that seems to require nested loops, I hope someone can
explain.
It seems obvious. You have a join clause:
'enheid.id_h_eenheid = huurovereenkomst_s._l_eenheid'
One side o
On Friday, October 10, 2025, wrote:
> Looking for help on storing and retrieving the personal data as masked.
> Any references and implementation details would help
>
Don’t reply to existing threads with unrelated stuff. Just send an email
to begin your own thread. And choose an appropriate pl
On 9/23/25 12:43, Andrei Lepikhov wrote:
But is it the same for the 'distinct' statistics? It seems you should
love it - the number of groups in GROUP-BY, DISTINCT, and even HashJoin
should be estimated more precisely, no?
I think it has more potential, and I would love to use this weapon,
On 23/9/2025 15:31, Frédéric Yhuel wrote:
To get back to the topic of partitioned statistics, do you know if SQL
Server is smart enough to handle this case [1] that we discussed last
year? (with filtered statistics)
[1] https://www.postgresql.org/message-id/flat/b860c71a-7cab-4d88-
ad87-8c1f2
On 13/10/2025 16:55, Tom Lane wrote:
Andrei Lepikhov writes:
On 25/9/2025 12:41, Frédéric Yhuel wrote:
So, on SQL Server, you can do this:
CREATE STATISTICS FooStats ON foo (ackid, crit) WHERE crit = 'WARNING';
Nice! Thanks for the report. I think the only reason why Postgres
doesn't have i
Looking for help on storing and retrieving the personal data as masked. Any
references and implementation details would help
> On 10 Oct 2025, at 3:24 AM, Peter Geoghegan wrote:
>
> On Fri, Feb 7, 2025 at 2:05 AM Jon Emord wrote:
>> My expectation is that the following two queries would have
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 plann
On 9/22/25 23:15, Andrei Lepikhov wrote:
I'm not sure I fully understand your case, but SQL Server demonstrates
an interesting approach: they have a WHERE clause attached to
statistics. So, having implemented this, you may separate the whole
range of values inside the table into 'partitions'
On 9/23/25 12:20, Frédéric Yhuel wrote:
On 9/22/25 23:15, Andrei Lepikhov wrote:
I'm not sure I fully understand your case, but SQL Server demonstrates
an interesting approach: they have a WHERE clause attached to
statistics. So, having implemented this, you may separate the whole
range of
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
Never mind my message. I misread it and missed the plan with no hints.
Michał
> On 25 Sep 2025, at 22:10, Michał Kłeczek wrote:
>
>
> Hi,
>
> Partition pruning is happening: pruned nodes are marked as “never executed”.
> It is just that pruning is performed not by the planner but by the ex
Hi,
Partition pruning is happening: pruned nodes are marked as “never executed”.
It is just that pruning is performed not by the planner but by the executor in
this case.
—
Michał
> On 25 Sep 2025, at 21:49, Lauro Ojeda wrote:
>
>
> Hi super-experts,
> I am trying to solve a mystery for a
On 9/23/25 15:31, Frédéric Yhuel wrote:
To get back to the topic of partitioned statistics, do you know if SQL
Server is smart enough to handle this case [1] that we discussed last
year? (with filtered statistics)
[1] https://www.postgresql.org/message-id/flat/b860c71a-7cab-4d88-
ad87-8c1f
Hi,
My first thought is pending list costs associated with the GIN indexes.
https://pganalyze.com/blog/gin-index
You may be able to use pageinspect's function gin_metapage_info to see what the
https://www.postgresql.org/docs/16/pageinspect.html#PAGEINSPECT-GIN-FUNCS
https://gitlab.com/gitlab-com/
On 23/9/2025 12:20, Frédéric Yhuel wrote:
On 9/22/25 23:15, Andrei Lepikhov wrote:
It may solve at least one issue with the 'dependencies' statistics: a
single number describing the dependency between any two values in the
columns often leads to incorrect estimations, as I see.
For what it's
On 9/22/25 15:57, Andrei Lepikhov wrote:
On 22/9/2025 15:37, Frédéric Yhuel wrote:
I wonder if this is an argument in favour of decoupling the sample
size and the precision of the statistics. Here, we basically want the
sample size to be as big as the table in order to include the few
(NULL
On 22/9/2025 15:37, Frédéric Yhuel wrote:
I wonder if this is an argument in favour of decoupling the sample size
and the precision of the statistics. Here, we basically want the sample
size to be as big as the table in order to include the few (NULL,
WARNING) values.
I also have seen how repea
On 9/20/25 18:51, Tom Lane wrote:
I concluded that maybe I was overthinking this part. We only really
need to check the rowcount estimate, since the indexscan cost estimate
is already okay. And stats_ext.sql seems to have gotten away with
assuming that rowcount estimates are reliably reprodu
=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= writes:
> On 9/18/25 18:40, Tom Lane wrote:
>> The attached fixes things so it works like it did pre-a391ff3c3.
> Indeed, it works well!
Thanks for testing!
>> I spent some time trying to devise a test case, and was reminded
>> of why I didn't have one befor
Matt Long writes:
> I finally got around to testing your patch on a realistic data set. In
> short, the patch worked beautifully even with the division by 2 removed. In
> case it's helpful, the full write up of my investigation can be found at
> https://gist.github.com/mattlong/0617bec6e1cf5bc6b70
I finally got around to testing your patch on a realistic data set. In
short, the patch worked beautifully even with the division by 2 removed. In
case it's helpful, the full write up of my investigation can be found at
https://gist.github.com/mattlong/0617bec6e1cf5bc6b70c6c2951901df7
Your reasoni
On Thu, 18 Sep 2025 12:59:11 -0400
Tom Lane wrote:
> Jehan-Guillaume de Rorthais writes:
> > On a fresh instance from HEAD with its default configuration, it shows:
>
> > Index Scan using foo_s_idx on foo (cost=0.29..8.39 rows=3 width=13)
> > Index Cond: (s(crit, ackid) = true)
>
>
On 19/9/2025 03:05, David Rowley wrote:
On Thu, 18 Sept 2025 at 23:55, Andrei Lepikhov wrote:
Perhaps we should start working on introducing this type of callback/ hook?
There's certainly places where you could add a hook that would just
add an unacceptable overhead that we couldn't stomach. I
On 9/18/25 18:40, Tom Lane wrote:
The attached fixes things so it works like it did pre-a391ff3c3.
Indeed, it works well!
I spent some time trying to devise a test case, and was reminded
of why I didn't have one before: it's hard to make a case that
will be robust enough to not show diffs
On Thu, 18 Sept 2025 at 23:55, Andrei Lepikhov wrote:
> It looks like a makeshift solution. By implementing a callback, we could
> elevate 'interrupter' to a first-class feature, enabling us to monitor
> the state of the entire query tree (it is especially cool in EXPLAIN
> ANALYZE mode when we ma
Thanks David,
Le 18/09/2025 à 09:20, David Rowley a écrit :
Yes. Since *all* records of "oeu" are required and they're not
required in any particular order, then Seq Scan should be the fastest
way to access those records.
Ok but then why is it doing it on the AD table? Is it because of the
nu
=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= writes:
> On 9/17/25 16:41, Tom Lane wrote:
>> =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= writes:
>>> 2) the number of estimated rows is completely off in the second EXPLAIN,
>>> whereas the planner could easily use the statistics of foo_f_idx.
>> Hmm, not sure abo
On Thu, 18 Sept 2025 at 18:36, Jean-Christophe BOGGIO
wrote:
> Insert on copyrightad (cost=613790.59..4448045.97 rows=0 width=0)
> -> Merge Join (cost=613790.59..4448045.97 rows=84972138 width=328)
> Merge Cond: (((c.imcompid)::numeric) = ip.sipa_ip_code)
> -> Sort (cost=357
Jehan-Guillaume de Rorthais writes:
> On a fresh instance from HEAD with its default configuration, it shows:
> Index Scan using foo_s_idx on foo (cost=0.29..8.39 rows=3 width=13)
> Index Cond: (s(crit, ackid) = true)
> It seems statistics shown in "pg_stats" view for function "s()" a
Em qui., 18 de set. de 2025 às 13:40, Tom Lane escreveu:
> I wrote:
> > Sigh ... so the answer is this used to work (since commit 39df0f150)
> > and then I carelessly broke it in commit a391ff3c3. If you try this
> > test case in versions 9.5..11 you get a spot-on rowcount estimate.
> > Serves m
I wrote:
> Sigh ... so the answer is this used to work (since commit 39df0f150)
> and then I carelessly broke it in commit a391ff3c3. If you try this
> test case in versions 9.5..11 you get a spot-on rowcount estimate.
> Serves me right for not having a test case I guess, but I'm astonished
> that
Hi there,
I think this discussion has a nice solution, thank you!
However, while poking around this issue yesterday, we also found something
surprising between estimated rows and costs when using a function. Bellow the
scenario to apply on top of Frederic's one to quickly expose the weirdness:
On 18/9/2025 13:35, David Rowley wrote:
On Thu, 18 Sept 2025 at 19:55, Andrei Lepikhov wrote:
Imagine if we had a hook within the ExecProcNode. In that scenario, we
could create a trivial extension that would stop the query after, let's
say, 10 minutes of execution and display the current state
On 18/9/2025 09:20, David Rowley wrote:
On Thu, 18 Sept 2025 at 18:36, Jean-Christophe BOGGIO
If it still takes a long time, you might try SET enable_mergejoin = 0;
and run the EXPLAIN ANALYZE SELECT .. part. That'll at least give us
more accurate row counts of what we're actually working with.T
On Thu, 18 Sept 2025 at 19:55, Andrei Lepikhov wrote:
> Imagine if we had a hook within the ExecProcNode. In that scenario, we
> could create a trivial extension that would stop the query after, let's
> say, 10 minutes of execution and display the current state. This would
> give us more reliable
On Thu, 18 Sept 2025 at 19:45, Jean-Christophe BOGGIO
wrote:
> Ok but then why is it doing it on the AD table? Is it because of the
> number of rows?
It's hard to tell as I don't have a clear view on which columns are
from which tables. Perhaps "ad" can Index Only Scan because all of
the columns
Thank you Laurenz and Tom! I'm going to quote Tom's email here:
On 9/17/25 16:41, Tom Lane wrote:
=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= writes:
Hello, in the following, I don't understand why:
1) the expression index isn't used in the first EXPLAIN
The planner doesn't look for multi-clause ma
Matt Long writes:
> Not to let perfect be the enemy of better, but we're facing a variant of
> this issue that would not be addressed by the proposed patch.
> ...
> In this case, the effects of the proposed patch are not applied since the
> most_common_elems array is not empty. I'm not a statistic
On 9/17/25 16:57, Frédéric Yhuel wrote:
Yes, Laurenz made a similar suggestion, but the problem is that I'm
mostly interested in the estimated number of output rows... because in
the real query, there's a very bad Hash Join above (the Nested Loop is
*much* faster).
BTW, I've also tested an
=?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= writes:
> Hello, in the following, I don't understand why:
> 1) the expression index isn't used in the first EXPLAIN
The planner doesn't look for multi-clause matches of that sort.
You could apply a little ju-jitsu perhaps:
regression=# EXPLAIN (ANALYZE, SUMM
On Wed, 2025-09-17 at 15:55 +0200, Frédéric Yhuel wrote:
> Hello, in the following, I don't understand why:
>
> 1) the expression index isn't used in the first EXPLAIN
>
> 2) the number of estimated rows is completely off in the second EXPLAIN,
> whereas the planner could easily use the statisti
I wrote:
> * The selectivity functions believe that the upper bound on the
> frequency of non-MCEs is minfreq / 2, not the stored minfreq.
> This seems like complete brain fade: there could easily be
> elements with frequency just less than minfreq, and probably are
> if the data distribution follo
Not to let perfect be the enemy of better, but we're facing a variant of
this issue that would not be addressed by the proposed patch. If you're
interested, the real world use case is described in
https://github.com/medplum/medplum/issues/7310. Rows have an array
consisting of one common element pr
Hi! Thank you for sharing this interesting case!
On 01.09.2025 12:07, Алексей Борщёв wrote:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SUMMARY, SETTINGS, TIMING)
SELECT
MIN(docum.dt) AS "dt__min",
MAX(docum.dt_real) AS "dt_real__max"
FROM docum
WHERE docum.dt_real >= '2025-08-14T09:44:09.0335
On Tue, 2 Sept 2025 at 00:41, Alena Rybakina wrote:
> After disabling MIN/MAX optimization in the grouping_planner function:
> /*
> * Preprocess MIN/MAX aggregates, if any. Note: be careful about
> * adding logic between here and the query_planner() call.
> Anything
>
Hi,
it is definitively possible to get nested loop joins on successively
aggregated CTEs. However, for the index to be used, it must exist. And
you can only create the index on a real table, not on the intermediate
CTEs.
> WITH series1h AS MATERIALIZED (SELECT generate_series AS ts FROM
> generat
On Tue, 2025-08-26 at 11:21 +0300, Ertan Küçükoglu wrote:
> I am using PostgreSQL 17.6 on Win64 platform running on VPS with 4 cores
> (2.59Ghz Xeon SapphireRapids) and 4GB RAM.
> On average 1.7 to 2.0GB of RAM is actively used on that server.
> Disk performance is not great at all.
> I have no oth
On 8/26/25 09:21, Ertan Küçükoglu wrote:
Hello,
I read a lot of different suggestions on the web and finally confused
and decided to ask in here.
Same/similar questions are asked before like 14 years ago 7 years ago,
etc. and I also wanted to learn the latest news.
I am using PostgreSQL 17
On 8/19/25 17:37, Frits Hoogland wrote:
The specific issue I see in certain cases leading to unreasonable swap usage is
Linux workingset detection kicking in
Do you have a way to highlight that precisely? I mean, can you prove
that it is Linux workingset detection that is causing swapping?
Thank you for your message Frederic,
I am very much aware of that issue. It’s actually incorrect to say that is a
bug: that is how cgroupsv1, which is bundled with rhel8, works. However, it is
very counter intuitive. For that reason redhat created the
force_cgroup_v2_swappiness parameter unique
On 8/8/25 10:21, Frits Hoogland wrote:
If swappiness is set to 0, but swap is available, some documentation
suggests it will never use anonymous memory, however I found this not to
be true, linux might still choose anonymous memory to reclaim.
A bug in RHEL8 meant that swappiness was not t
available
Get Outlook for iOS<https://aka.ms/o0ukef>
From: Bruce Momjian
Sent: Tuesday, August 12, 2025 1:49:26 PM
To: Frits Hoogland
Cc: Joe Conway ; Priya V ;
[email protected]
Subject: Re: Safe vm.overcommit_ratio for Large
On Wed, Aug 6, 2025 at 11:14:34PM +0200, Frits Hoogland wrote:
> > As I said, do not disable swap. You don't need a huge amount, but maybe 16
> > GB or so would do it.
>
> Joe, please, can you state a technical reason for saying this?
> All you are saying is ‘don’t do this’.
>
> I’ve stated my
Joe, I am trying to help, and make people think about things correctly.
The linux kernel is actually constantly changing, sometimes subtle and
sometimes less subtle, and there is a general lack of very clear statistics
indicating the more nuanced memory operations, and the documentation about it
On 8/6/25 17:14, Frits Hoogland wrote:
As I said, do not disable swap. You don't need a huge amount, but
maybe 16 GB or so would do it.
Joe, please, can you state a technical reason for saying this?
All you are saying is ‘don’t do this’.
I’ve stated my reasons for why this doesn’t make sense,
> As I said, do not disable swap. You don't need a huge amount, but maybe 16 GB
> or so would do it.
Joe, please, can you state a technical reason for saying this?
All you are saying is ‘don’t do this’.
I’ve stated my reasons for why this doesn’t make sense, and you don’t give any
reason.
The
(Both: please trim and reply inline on these lists as I have done;
Frits, please reply all not just to the list -- I never received your
reply to me)
On 8/6/25 11:51, Priya V wrote:
*cat /proc/sys/vm/overcommit_ratio*
50
$ *cat /proc/sys/vm/swappiness*
60
*Workload*: Multi-tenant PostgreSQL
Hi Frits, Joe,
Thank you both for you insights
*Current situation:*
*cat /proc/sys/vm/overcommit_memory*
0
*cat /proc/sys/vm/overcommit_ratio*
50
$ *cat /proc/sys/vm/swappiness*
60
*Workload*: Multi-tenant PostgreSQL
*uname -r*
4.18.0-477.83.1.el8_8.x86_64
*free -h*
total used free shared b
Joe,
Can you name any technical reason why not having swap for a database is an
actual bad idea?
Memory always is limited. Swap was invented to overcome a situation where the
(incidental) memory usage of paged in memory was could (regularly) get higher
than physical memory would allow, and th
On 8/5/25 13:01, Priya V wrote:
*Environment:*
*PostgreSQL Versions:* Mix of 13.13 and 15.12 (upgrades in progress
to be at 15.12 currently both are actively in use)
PostgreSQL 13 end of life after November 13, 2025
*OS / Kernel:* RHEL 7 & RHEL 8 variants, kernels in the 4.14–4.18
o leverage in more cases.
And as far as I can tell, if there is no TRUNCATE in the same
transaction, then pg_restore will output error like the following:
ERROR: cannot perform COPY FREEZE because the table was not created or
truncated in the current subtransaction
I hope such an erroris acceptable,
On Mon, Jul 21, 2025 at 12:24 PM Dimitrios Apostolou wrote:
> >
> > FWIW I implemented a pg_restore --freeze patch, see attached. It needs
> > another patch of mine from [1] that implements pg_restore --data-only
> > --clean, which for parallel restores encases each COPY in its own
> transaction
"=?utf-8?B?WHVhbiBDaGVu?=" writes:
> In some cases, I noticed that a parent path node's total cost is less than
> that of one of its child path nodes. I initially expected that the total cost
> of a node should be at least as large as the sum of its child nodes’ total
> costs, or at least not s
I wrote:
> Well, we don't have a most common element in this scenario --- the
> whole point is that the occurrence counts resulting from the lossy
> counting algorithm are too low to be trustworthy. However, what we
> do have is the cutoff frequency, and it seems to me that we could use
> that as
We just jdbc and bind variables, so we are using PreparedStatements.
plan_cache_mode is set to auto
So, it sounds like there could be plan caching. (I wasn't aware of that.)
Is there any kind of running counter in a system view that tracks the
number of executions of cached plans?
We are capturing
Jerry Brenner writes:
> I don't have any background with the randomized search. Does the repeated
> pattern with the same plan being executed multiple times in a time range
> and then the plan changes, never to change back, match the expectation with
> the randomization?
[ shrug... ] Insufficie
Thanks for the quick response!
I don't have any background with the randomized search. Does the repeated
pattern with the same plan being executed multiple times in a time range
and then the plan changes, never to change back, match the expectation with
the randomization?
Thanks,
Jerry
On Thu, J
1 - 100 of 1861 matches
Mail list logo
