On 7/28/25 16:47, Nathan Bossart wrote:
I can't remember who wrote this line, but it was borrowed from the
--analyze-in-stages description. The point is that if you use
--analyze-in-stages without --missing-stats-only, there will be a period
where existing statistics will be replaced with one
On 7/26/25 21:38, Corey Huinker wrote:
It is transactional, mostly.
The attribute stats for the table being analyzed and all attribute stats
for the dependent indexes that have at least one expression column, plus
extended stats objects on that table, will be replaced in one atomic
opera
On 3/18/25 22:37, Nathan Bossart wrote:
Committed with the following small changes:
Hi, I don't really understand this sentence in
doc/src/sgml/ref/vacuumdb.sgml:
> This option prevents vacuumdb from deleting existing statistics so
that the query optimizer's choices do not become transie
On 7/4/25 18:00, Mircea Cadariu wrote:
Just attaching v2 of the patch.
Hi Mircea,
Your patch applies cleanly and seems to work well.
IIUC, the index hit ratio should be computed with the following formula:
(idx_blks_hit - idx_metadata_blks) / (idx_blks_hit - idx_metadata_blks +
idx_blks_
On 7/9/25 16:30, Frédéric Yhuel wrote:
On 7/9/25 16:06, Mihail Nikalayeu wrote:
Hello!
No, I think the comment is correct, it is about [0].
[0]: https://github.com/postgres/postgres/blob/
f5a987c0e5f6bbf0cc0420228dc57e7aae4d7e8f/src/backend/commands/
indexcmds.c#L4217
Aaahhh... yes
On 7/9/25 16:06, Mihail Nikalayeu wrote:
Hello!
No, I think the comment is correct, it is about [0].
[0]:
https://github.com/postgres/postgres/blob/f5a987c0e5f6bbf0cc0420228dc57e7aae4d7e8f/src/backend/commands/indexcmds.c#L4217
Aaahhh... yes, you're right! thanks!
Hi,
I believe that the description of which transactions will block a CREATE
INDEX CONCURRENTLY command from proceeding isn't right.
Please find the attached patch.
Best regards,
FrédéricFrom cf3ecfda5155584e7c1d81c5daaf04e78d4fddef Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=
On 4/26/25 20:57, Sami Imseih wrote:
I found several issues with v4. It does not deal correctly with pipelining,
and we should only really be concerned with dropping an unnamed
portal only.
So, v5 now moves the DropPortal call after the unnamed portal was
executed to completion ( as v4 was doi
On 4/23/25 18:13, Sami Imseih wrote:
Also, another strange behavior of the way portal cleanup occurs is that
in extended-query-protocol and within a transaction, ExecutorEnd for the
last query is not actually called until the next command. This just seems
odd to me especially for extensions th
On 4/23/25 09:41, Frédéric Yhuel wrote:
On 4/22/25 19:37, Sami Imseih wrote:
the patch relies on looking up queryDesc->sourceText inside DropPortal,
which Tom raised concerns about earlier in the thread [0]
Yes, I think I had misunderstood what Tom said. Thank you for pointing
that
On 4/22/25 19:37, Sami Imseih wrote:
the patch relies on looking up queryDesc->sourceText inside DropPortal,
which Tom raised concerns about earlier in the thread [0]
Yes, I think I had misunderstood what Tom said. Thank you for pointing
that out.
However, is it really unsafe?
In exec_bi
On 4/21/25 07:46, Frédéric Yhuel wrote:
On 4/20/25 00:42, Sami Imseih wrote:
(In my testing, the "temporary file:" message comes
out without any attached STATEMENT most of the time already, so this
isn't losing much as far as that's concerned.)
Indeed, this happens w
On 4/21/25 07:46, Frédéric Yhuel wrote:
I can try to implement Tom's idea if we have a consensus.
v3 attached. Would that do?From 07d331ba0f91b999fcefd12696bfc1eda7e8f20f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?=
Date: Fri, 18 Apr 2025 13:20:52 +0200
Su
On 4/20/25 00:42, Sami Imseih wrote:
(In my testing, the "temporary file:" message comes
out without any attached STATEMENT most of the time already, so this
isn't losing much as far as that's concerned.)
Indeed, this happens when using autocommit / implicit transactions.
But if you disabl
On 4/18/25 10:49, Frédéric Yhuel wrote:
Hi,
It seems there's a bug in the logging of temporary file usage when the
extended protocol is used with unnamed portals.
FWIW, the attached patch seems to fix the problem.
Best regards,
FrédéricFrom afb228f07f847c467ba05dbe204861e7be2ffc3
Hi,
It seems there's a bug in the logging of temporary file usage when the
extended protocol is used with unnamed portals.
For example, with the attached Java / pgJDBC programs, we get the
following logs:
[...] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp525566.0",
size 2416640
[
On 3/14/25 09:43, Frédéric Yhuel wrote:
One thing I've noticed is that posix_fadvise(,,POSIX_FADV_WILLNEED)
isn't always non-blocking on Linux. As Ted Ts'o explains in this old
thread[1], it blocks when the request queue fills up.
When posix_fadvise() blocks, it doe
Hi,
I'm currently looking at how we can integrate effective_io_concurrency
into the planner cost model. This is actually a big overstatement, as I
have only done a bunch of tests so far. One thing I've noticed is that
posix_fadvise(,,POSIX_FADV_WILLNEED) isn't always non-blocking on Linux.
As
On 1/27/25 20:56, Ants Aasma wrote:
I'll set the patch "ready for committer".
Thanks!
I personally would still like to know how fragmentation slows down performance.
Probable reason is that scanning an unfragmented index results in
sequential I/O patterns that the kernel read-ahead mechan
to guess what leaf_fragmentation meant without looking
at pgstattuple's code. This patch aims to give to the user a better
idea of what it means.
Author: Frédéric Yhuel
Author: Laurenz Albe
Reviewed-by: Bertrand Drouvot, Benoît Lobréau
Discussion: https://postgr.es/m/bf110561-f774-4957-a890
On 1/24/25 11:47, Bertrand Drouvot wrote:
Hi Frédéric,
On Thu, Jan 23, 2025 at 10:00:27AM +0100, Frédéric Yhuel wrote:
On 1/22/25 12:34, Bertrand Drouvot wrote:
I'm not sure it's good to describe something as the inverse of "something
else". See my proposal below.
Yea
On 1/22/25 12:34, Bertrand Drouvot wrote:
Hi,
On Tue, Nov 05, 2024 at 06:36:47PM +0100, Frédéric Yhuel wrote:
Hi, I thought it would be nice to give the user a better idea of what
avg_leaf_density and leaf_fragmentation mean.
Patch attached. What do you think?
Yeah, I think that can not
On 1/22/25 10:24, Benoit Lobréau wrote:
Here is an updated patch to save you some time.
Thanks ! it does seems better that way.
On 1/7/25 23:57, Nathan Bossart wrote:
Here is a rebased patch for cfbot. AFAICT we are still pretty far from
consensus on which approach to take, unfortunately.
For what it's worth, although I would have preferred the sub-linear
growth thing, I'd much rather have this than nothing.
And
On 11/9/24 16:59, Nathan Bossart wrote:
AFAICT the main advantage of these formulas is that you don't need another
GUC, but they also makes the existing ones more difficult to configure.
I wouldn't say that's the main advantage. It doesn't seem very clean to
me to cap to a fixed value. Beca
On 11/4/24 09:30, wenhui qiu wrote:
Hi hackers
A few days ago, I was looking at the sql server documentation and
found that sql server has optimized the algorithm related to updating
statistics in the 2016 ,version,I think we can also learn from the
implementation method of sql server
Hi, I thought it would be nice to give the user a better idea of what
avg_leaf_density and leaf_fragmentation mean.
Patch attached. What do you think?From 5c82c207776fb8cde2357081b8579ba6db195c06 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?=
Date: Tue, 5 Nov 2024 17:59:4
Le 12/04/2023 à 20:06, Robert Haas a écrit :
There's only one existing test case that visibly changes plan with
these changes. The new plan is clearly saner-looking than before,
and testing with some data loaded into the table confirms that it
is faster. I'm not sure if it's worth devising m
On 9/7/24 22:45, Tom Lane wrote:
I wrote:
Now alternatively you could argue that a "new" page isn't usable free
space yet and so we should count it as zero, just as we don't count
dead tuples as usable free space. You need VACUUM to turn either of
those things into real free space. But that'
Hi Tom, thanks for your review.
On 9/7/24 22:10, Tom Lane wrote:
I looked at this patch. I agree with making the change. However,
I don't agree with the CF entry's marking of "target version: stable"
(i.e., requesting back-patch). I think this falls somewhere in the
gray area between a bug fi
On 8/23/24 12:51, Frédéric Yhuel wrote:
On 8/23/24 12:02, Rafia Sabih wrote:
On the other hand, this got me thinking about the purpose of this
space information.
If we want to understand that there's still some space for the tuples
in a page, then using PageGetExactFreeSpace is not
On 8/23/24 12:02, Rafia Sabih wrote:
On the other hand, this got me thinking about the purpose of this space
information.
If we want to understand that there's still some space for the tuples in
a page, then using PageGetExactFreeSpace is not doing justice in case of
heap page, because we wi
On 8/22/24 21:56, Rafia Sabih wrote:
I agree with the approach here.
A minor comment here is to change the comments in code referring to the
PageGetHeapFreeSpace.
Thank you Rafia. Here is a v2 patch.
I've also added this to the commit message:
Also, PageGetHeapFreeSpace() will return zero
Hello,
I think that pgstattuple should use PageGetExactFreeSpace() instead of
PageGetHeapFreeSpace() or PageGetFreeSpace(). The latter two compute the
free space minus the space of a line pointer. They are used like this in
the rest of the code (heapam.c):
pagefree = PageGetHeapFreeSpace(pag
f 51M currently) for a 256M tuples table ;
* 3M (instead of 5M currently) for a 25.6M tuples table.
The other advantage is that you don't need another GUC.
On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
By the way, I wonder if there were any off-list discussions after Ro
Le 18/06/2024 à 05:06, Nathan Bossart a écrit :
I didn't see a commitfest entry for this, so I created one to make sure we
don't lose track of this:
https://commitfest.postgresql.org/48/5046/
OK thanks!
By the way, I wonder if there were any off-list discussions after
Robert's co
Le 09/05/2024 à 16:58, Robert Haas a écrit :
As I see it, a lot of the lack of agreement up until now is people
just not understanding the math. Since I think I've got the right idea
about the math, I attribute this to other people being confused about
what is going to happen and would tend to
Le 01/05/2024 à 20:50, Robert Haas a écrit :
Possibly what we need here is
something other than a cap, where, say, we vacuum a 10GB table twice
as often as now, a 100GB table four times as often, and a 1TB table
eight times as often. Or whatever the right answer is.
IMO, it would make more s
Le 25/04/2024 à 22:21, Robert Haas a écrit :
The analyze case, I feel, is really murky.
autovacuum_analyze_scale_factor stands for the proposition that as the
table becomes larger, analyze doesn't need to be done as often. If
what you're concerned about is the frequency estimates, that's true:
Le 26/04/2024 à 04:24, Laurenz Albe a écrit :
On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
I believe that the underlying problem here can be summarized in this
way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
mean that I'm OK with 2TB of bloat in my 10TB table. One
Le 25/04/2024 à 18:51, Melanie Plageman a écrit :
I'm not too sure I understand. What are the reasons it might by skipped?
I can think of a concurrent index creation on the same table, or
anything holding a SHARE UPDATE EXCLUSIVE lock or above. Is this the
sort of thing you are talking about?
Le 25/04/2024 à 21:21, Nathan Bossart a écrit :
On Thu, Apr 25, 2024 at 02:33:05PM -0400, Robert Haas wrote:
What does surprise me is that Frédéric suggests a default value of
500,000. If half a million tuples (proposed default) is 20% of your
table (default value of autovacuum_vacuum_scale_f
Hi Nathan, thanks for your review.
Le 24/04/2024 à 21:57, Nathan Bossart a écrit :
Yeah, I'm having trouble following the proposed mechanics for this new GUC,
and it's difficult to understand how users would choose a value. If we
just want to cap the number of tuples required before autovacuum
Le 24/04/2024 à 21:10, Melanie Plageman a écrit :
On Wed, Apr 24, 2024 at 8:08 AM Frédéric Yhuel
wrote:
Hello,
I would like to suggest a new parameter, autovacuum_max_threshold, which
would set an upper limit on the number of tuples to delete/update/insert
prior to vacuum/analyze.
Hi
Hello,
I would like to suggest a new parameter, autovacuum_max_threshold, which
would set an upper limit on the number of tuples to delete/update/insert
prior to vacuum/analyze.
A good default might be 50.
The idea would be to replace the following calculation :
vacthresh = (float4) vac
Le 21/12/2023 à 14:29, Laurenz Albe a écrit :
Here is a patch to implement this.
Being stuck behind a lock for more than a second is almost
always a problem, so it is reasonable to turn this on by default.
I think it's a really good idea. At Dalibo, we advise our customers to
switch it on.
Hi Daniel,
Thank you for your answer.
On 9/28/23 14:02, Daniel Gustafsson wrote:
On 28 Sep 2023, at 10:14, Frédéric Yhuel wrote:
After some time, we understood that the 20 million of large objects were
responsible for the huge memory usage (more than 10 GB) by pg_dump.
This sounds like
Hello,
One of our customers recently complained that his pg_dump stopped
abruptly with the message "out of memory".
After some time, we understood that the 20 million of large objects were
responsible for the huge memory usage (more than 10 GB) by pg_dump.
I think a more useful error messag
On 8/17/23 14:00, Frédéric Yhuel wrote:
On 8/17/23 09:32, Frédéric Yhuel wrote:
On 8/10/23 17:06, Juan José Santamaría Flecha wrote:
Recently I restored a database from a directory format backup and
having this feature would have been quite useful
Hi,
Thanks for resuming work on this
On 8/17/23 09:32, Frédéric Yhuel wrote:
On 8/10/23 17:06, Juan José Santamaría Flecha wrote:
Recently I restored a database from a directory format backup and
having this feature would have been quite useful
Hi,
Thanks for resuming work on this patch. I forgot to mention this in my
On 8/10/23 17:06, Juan José Santamaría Flecha wrote:
Recently I restored a database from a directory format backup and having
this feature would have been quite useful
Hi,
Thanks for resuming work on this patch. I forgot to mention this in my
original email, but the motivation was also to
On 3/20/23 15:58, Gregory Stark (as CFM) wrote:
On Mon, 12 Dec 2022 at 11:37, Frédéric Yhuel wrote:
I've planned to work on it full time on week 10 (6-10 March), if you
agree to bear with me. The idea would be to bootstrap my brain on it,
and then continue to work on it from time to
On 12/11/22 06:29, Ian Lawrence Barwick wrote:
2022年7月26日(火) 20:58 Frédéric Yhuel :
On 4/14/22 14:25, Frédéric Yhuel wrote:
On 3/19/22 01:57, Imseih (AWS), Sami wrote:
I looked at your patch and it's a good idea to make foreign key
validation
use parallel query on large relations
On 11/23/22 16:59, Tom Lane wrote:
=?UTF-8?Q?Fr=c3=a9d=c3=a9ric_Yhuel?= writes:
On 10/24/22 17:26, Frédéric Yhuel wrote:
When studying the weird planner issue reported here [1], I came up with
the attached patch. It reduces the probability of calling
get_actual_variable_range().
This
On 10/24/22 17:26, Frédéric Yhuel wrote:
Hello,
When studying the weird planner issue reported here [1], I came up with
the attached patch. It reduces the probability of calling
get_actual_variable_range().
This isn't very useful anymore thanks to this patch:
https://git.postgresq
On 10/24/22 17:26, Frédéric Yhuel wrote:
Hello,
When studying the weird planner issue reported here [1], I came up with
the attached patch. It reduces the probability of calling
get_actual_variable_range().
The patch applies to the master branch.
How to test :
CREATE TABLE foo (a
Hi,
Here are a few more things I noticed :
If a CEK is encrypted with cmk1 and cmk2, but cmk1 isn't found on the
client,the following error is printed twice for the very first SELECT
statement:
could not open file "/path/to/cmk1.pem": No such file or directory
...and nothing is returned.
Hello,
When studying the weird planner issue reported here [1], I came up with
the attached patch. It reduces the probability of calling
get_actual_variable_range().
The patch applies to the master branch.
How to test :
CREATE TABLE foo (a bigint, b TEXT) WITH (autovacuum_enabled = off);
IN
On 10/24/22 03:01, Tom Lane wrote:
=?UTF-8?Q?Fr=c3=a9d=c3=a9ric_Yhuel?= writes:
When using pg_dump (or pg_restore) with option "--clean", there is some
SQL code to drop every objects at the beginning.
Yup ...
The DROP statement for a view involving circular dependencies is :
CREATE OR REPLA
Hello,
When using pg_dump (or pg_restore) with option "--clean", there is some
SQL code to drop every objects at the beginning.
The DROP statement for a view involving circular dependencies is :
CREATE OR REPLACE VIEW [...]
(see commit message of d8c05aff for a much better explanation)
If t
On 4/14/22 14:25, Frédéric Yhuel wrote:
On 3/19/22 01:57, Imseih (AWS), Sami wrote:
I looked at your patch and it's a good idea to make foreign key
validation
use parallel query on large relations.
It would be valuable to add logging to ensure that the ActiveSnapshot
On 3/19/22 01:57, Imseih (AWS), Sami wrote:
I looked at your patch and it's a good idea to make foreign key validation
use parallel query on large relations.
It would be valuable to add logging to ensure that the ActiveSnapshot and
TransactionSnapshot
is the same for the leader and the worke
On 4/11/22 02:57, Michael Paquier wrote:
On Fri, Apr 08, 2022 at 04:23:48PM +0200, Frédéric Yhuel wrote:
Thank you Michael.
And done as of 8ac700a.
--
Thank you Micheal!
For reference purposes, we can see in the code of get_relation_info(),
in plancat.c, that indeed every index of the
On 4/8/22 02:22, Michael Paquier wrote:
On Thu, Apr 07, 2022 at 05:29:36PM +0200, Guillaume Lelarge a écrit :
Le jeu. 7 avr. 2022 à 15:44, Frédéric Yhuel a écrit
:
On 4/7/22 14:40, Justin Pryzby wrote:
Thank you Justin! I applied your fixes in the v2 patch (attached).
v2 patch sounds
On 4/7/22 14:40, Justin Pryzby wrote:
On Thu, Apr 07, 2022 at 01:37:57PM +0200, Frédéric Yhuel wrote:
Maybe something along this line? (patch attached)
Some language fixes.
Thank you Justin! I applied your fixes in the v2 patch (attached).
I didn't verify the behavior, but +1 to doc
On 4/6/22 17:03, Peter Geoghegan wrote:
On Wed, Apr 6, 2022 at 7:49 AM Frédéric Yhuel wrote:
From the documentation
(https://www.postgresql.org/docs/current/sql-reindex.html#id-1.9.3.162.7),
it sounds like REINDEX won't block read queries that don't need the
index. But it seem
Hello,
From the documentation
(https://www.postgresql.org/docs/current/sql-reindex.html#id-1.9.3.162.7),
it sounds like REINDEX won't block read queries that don't need the
index. But it seems like the planner wants to take an ACCESS SHARE lock
on every indexes, regardless of the query, and s
Hello, sorry for the late reply.
On 2/14/22 15:33, Robert Haas wrote:
On Mon, Feb 7, 2022 at 5:26 AM Frédéric Yhuel wrote:
I noticed that referential integrity checks aren't currently
parallelized. Is it on purpose?
It's not 100% clear to me that it is safe. But on the other hand,
On 2/11/22 00:16, Andreas Karlsson wrote:
On 2/7/22 11:26, Frédéric Yhuel wrote:
Attached is a (naive) patch that aims to fix the case of a FK
addition, but the handling of the flag CURSOR_OPT_PARALLEL_OK,
generally speaking, looks rather hackish.
Thanks, for the patch. You can add it to
Hello,
I was wondering if pg_restore should call VACUUM ANALYZE for all tables,
after the "COPY" stage, and before the "post-data" stage.
Indeed, without such a VACUUM, the visibility map isn't available.
Depending on the size of the tables and on the configuration, a foreign
key constraint
Hello,
I noticed that referential integrity checks aren't currently
parallelized. Is it on purpose?
From the documentation [1], the planner will not generate a parallel
plan for a given query if any of the following are true:
1) The system is running in single-user mode.
2) max_parallel_wor
71 matches
Mail list logo
