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 ;
pgsql-performance@lists.postgresql.org
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
Jerry Brenner writes:
> We are on Postgres 15.5 (Aurora) and capturing query plans via
> auto_explain. We are seeing a large number of query plans for 2 queries
> that have 12 tables. Every fast (or "fast enough") plan has a left deep
> tree and every slow plan has a bushy tree. Is there a way
On Thu, Jul 17, 2025 at 18:11 Jerry Brenner wrote:
> We are on Postgres 15.5 (Aurora) and capturing query plans via
> auto_explain. We are seeing a large number of query plans for 2 queries
> that have 12 tables. Every fast (or "fast enough") plan has a left deep
> tree and every slow plan has
Following up on this, I very often have to create PLPgSql functions to
workaround this problem: create one (or several) temp table(s) (with ON
COMMIT DROP), analyze it/them and create indices on some field(s).
Being able to write something like:
WITH xxx AS MATERIALIZED ANALYZED INDEXED ON fie
Thanks Alvaro,
I read the thread and actually disabled truncate on my table with a large
toast table which mitigated the issue. Unsure what happens with the empty
pages now, I guess they would be reused for new inserts.
I would like to see if there are better opportunities to improve this
truncati
On 2025-Jul-08, Dharin Shah wrote:
> *Problem Summary:*
> WAL replay of relation truncation operations on read replicas triggers
> buffer invalidation that requires AccessExclusive locks, blocking
> concurrent read queries for extended periods.
Hmm, sounds like disabling truncate of the TOAST r
It's hard to give generic recommendations for what really depends on your
specific needs, but here is one attempt:
using HikariCP for connection pooling.
For better scaling, look into PGBouncer, which has very fast "transaction"
and "statement" modes.
... manage 10-12 TB of data in a production
On Thu, Jun 26, 2025 at 2:48 AM James Pang wrote:
>we faced this issue 3 times this week, each time last only 2 seconds, so
> not easy to run perf in peak business time to capture that, anyway, I will
> try. before that, I want to understand if "os page cache" or "pg buffer
> cache" can con
thanks for you explaination, from Postgresql perspective, is it possible
to see bgwriter,or checkpointer blocking backend process reading/wrting ?
or vice versa ?
Thanks,
James
Frits Hoogland 於 2025年6月26日週四 下午4:07寫道：
> Postgres lives as a process in linux, and keeps its own cache, and tries
Postgres lives as a process in linux, and keeps its own cache, and tries to use
that as much as possible for data. This is postgres shared buffers, commonly
called the buffer cache.
For WAL, sessions write to the wal buffer (separate from the postgres buffer
cache), and need to write to disk u
in addition to "DataFileRead", actually we have more session waiting on
"extend", and we enabled log_lock, for example
2025-06-24 18:00:11.368 :[1865315]:[4-1]:mbsLOG: process 1865315 still
waiting for ExclusiveLock on extension of relation 14658239 of database
16384 after 1000.161 ms
2025-06-
we faced this issue 3 times this week, each time last only 2 seconds, so
not easy to run perf in peak business time to capture that, anyway, I will
try. before that, I want to understand if "os page cache" or "pg buffer
cache" can contribute to the wait_event time "extend" and "DataFileRead",
or
Okay. So it's a situation that is reproducable.
And like was mentioned, the system time (percentage) is very high.
Is this a physical machine, or a virtual machine?
The next thing to do, is use perf to record about 20 seconds or so during a
period of time when you see this behavior (perf record -
Thanks, I make a summary of the issue, no connection storm(fork)
either, just suddenly many session waiting on "extend" and "DataFileRead",
it last 2 seconds, this server has 64 vcpu and running there long time
without issue, only last weekend, we patch from 14.8 to 14.14. We checked
with Infr
> On Thu, 2025-06-26 at 10:32 +0800, James Pang wrote:
>> thans for you suggestions, we have iowait from sar command too, copy here,
>> checking with infra team not found abnormal IO activities either.
>> 02:00:01 PM CPU %usr %nice %sys %iowait %irq %soft %steal
>> %guest %gnic
On Thu, 2025-06-26 at 10:32 +0800, James Pang wrote:
> thans for you suggestions, we have iowait from sar command too, copy here,
> checking with infra team not found abnormal IO activities either.
> 02:00:01 PM CPU %usr %nice %sys %iowait %irq %soft %steal
> %guest %gnice %i
thans for you suggestions, we have iowait from sar command too, copy here,
checking with infra team not found abnormal IO activities either.
02:00:01 PM CPU%usr %nice%sys %iowait%irq %soft %steal
%guest %gnice %idle
02:00:03 PM all 15.920.00 43.020.650.7
You use temporary tables extensively.
>
>
David J.
> On 25 Jun 2025, at 07:59, Laurenz Albe wrote:
>
> On Wed, 2025-06-25 at 11:15 +0800, James Pang wrote:
>> pgv14, RHEL8, xfs , we suddenly see tens of sessions waiting on
>> "DataFileRead" and
>> "extend", it last about 2 seconds(based on pg_stat_activity query) , during
>> the
>> waiting t
transparent_hugepage=never in our prod servers, %iowait is low 0.x-1.x%
, read/write iops <2k, and read/write wait 0.x ms. we did not find other
abnormal logs from OS logs either. Yes, we are discussing with our
application team to reduce concurrency. more questions about DataFileRead
and ext
On Wed, 2025-06-25 at 11:15 +0800, James Pang wrote:
> pgv14, RHEL8, xfs , we suddenly see tens of sessions waiting on
> "DataFileRead" and
> "extend", it last about 2 seconds(based on pg_stat_activity query) , during
> the
> waiting time, "%sys" cpu increased to 80% , but from "iostat" , no high
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
and prepends it with a TRUNCATE. All feedback is welcome.
[1]
https://ww
On Fri, 12 Apr 2024, Thomas Munro wrote:
On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou wrote:
So would it make sense for postgres to perform reads in bigger blocks? Is it
easy-ish to implement (where would one look for that)? Or must the I/O unit be
tied to postgres' page size?
FYI as
Mark Frost writes:
> Actually *any* most_common_elems stats would be fine, because the reasoning
> is:
> * If the searched element is in most_common_elems we know it's frequency
> * If it's not, it's less frequent than the least most_common_elems
> So in our case when every row is unique,
> On 6/5/25 17:42, Mark Frost wrote:
> > Is there any good explanation for this behaviour? Preferably we’d like
> > some way for proper `most_common_elems` statistics to be collected in
> > our production database, in the hope that influences a good query plan
> > to always be selected.
> most_c
On 6/5/25 23:52, Tom Lane wrote:
The idea of treating lack of MCELEM differently from complete
lack of stats still seems to have merit, though.
Couldn't we count / estimate the number of distinct two-by-two elements,
and use that instead of the default selectivity estimate?
I wrote:
> The part of that that seems to be going off the rails is
> this selection of a cutoff frequency below which element values
> will be dropped:
> cutoff_freq = 9 * element_no / bucket_width;
> The first thing I find suspicious here is that the calculation is
> based on element_no
Mark Frost writes:
> We're seeing intermittently very poor performance of a query, when
> occasionally a poor query plan is chosen. We're using Postgres 16.9.
> One suspicious factor when looking at the EXPLAIN ANALYZE output, is a very
> wrong estimated number of rows to be returned from a text
Here's what the OP of the PostgresNIO issue has mentioned about what
performance impact usage of `file_copy` has in his setup
(https://github.com/SwiftPackageIndex/SwiftPackageIndex-Server/pull/3812):
`Series: Default Strategy
Suite AllTests passed after 5.081 seconds
Suite AllTests passed after
On 6/5/25 17:42, Mark Frost wrote:
Is there any good explanation for this behaviour? Preferably we’d like
some way for proper `most_common_elems` statistics to be collected in
our production database, in the hope that influences a good query plan
to always be selected.
most_common_elems h
On 6/5/25 16:13, Frédéric Yhuel wrote:
On 6/4/25 16:12, Dimitrios Apostolou wrote:
In general I have noticed most operations are slower after a succesful
pg_restore until VACUUM is complete, which is unfortunate as the
database is huge and it takes days to run. Something I have on my list
On 6/4/25 16:12, Dimitrios Apostolou wrote:
In general I have noticed most operations are slower after a succesful
pg_restore until VACUUM is complete, which is unfortunate as the
database is huge and it takes days to run. Something I have on my list
to try, is whether a COPY FREEZE would al
On Wed, 4 Jun 2025, Frédéric Yhuel wrote:
On 6/3/25 17:34, Dimitrios Apostolou wrote:
The backend process for each of the above ALTER TABLE commands, does not
parallelize the foreign key checks for the different partitions. I
know, because in the logs I see gigabytes of temporary files
On 6/3/25 17:34, Dimitrios Apostolou wrote:
The backend process for each of the above ALTER TABLE commands, does not
parallelize the foreign key checks for the different partitions. I
know, because in the logs I see gigabytes of temporary files being
written, with the CONTEXT showing
To be clear the "15s instead of 1m30s" was related to another issue about SASL
implementation of PostgresNIO being slow, not this CREATE DATABASE strategy
change. I was just trying to explain that in their set up, these saved
milliseconds do matter for whatever reason.
On Wed, May 28, 2025 at 11:09 PM Mahdi Bahrami wrote:
> their tests now run in 15s instead of 1m30s
Wow. If/when they get to v18, they might also try setting
file_copy_method=clone when using strategy=file_copy.
Thanks for the hint, that was exactly it! I also asked the original author of
the issue and he has confirmed the performance of pg 15+ now matches those of
pg 13/14.
As mentioned "This issue is not necessarily a big deal to us, but can anyone
please explain the why?" so it was partially out of
On 2025-May-27, Mahdi Bahrami wrote:
> Here's how I ran the commands that seem to be at fault when using
> PostgresNIO. I only ran the drop-db create-db commands, not the whole
> chain, which should still be sufficient:
> ```
> PGPASSWORD=xxx psql -U spi_test -h 127.0.0.1 postgres -c "DROP DATABAS
On 5/12/25 16:04, Maxim Boguk wrote:
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov If I'm not mistaken, it will work with all PG versions that are
currently in support. What do you think?
Such extension would be very useful (and in general - the solution based
on the actual execution
Thanks, we are checking the partition maintain job , we have 12 partitions
, each week one partition there is a default partition attached with this
table and huge rows in default partition too , default partition has
primary key that include partition key (time range based) too. partition
job det
thanks, we are checking partition maintain jobs ,that hold access
exclusive lock.
Tom Lane 於 2025年5月15日週四 下午9:24寫道：
> Laurenz Albe writes:
> > On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:
> >> why inserts into partition table cause "relation lock" ?
>
> > Something else does; use the
Laurenz Albe writes:
> On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:
>> why inserts into partition table cause "relation lock" ?
> Something else does; use the pg_blocking_pids() function with the process ID
> of
> a blocked backend to find out who is holding the lock.
More specifically:
On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:
> PGv 15.10, many session coming in doing "insert into tablexxx values" in
> parallel,
> this is a range partition tables, total 12 partitions plus one default
> partition,
> only three btree indexes, no others constraints ,no foreign key.
Hi James,
Could share the related test setup details?
Would like to look into this.
Thanks,
Chetan
On Thu, 15 May 2025 at 09:27, James Pang wrote:
> Hi,
>PGv 15.10, many session coming in doing "insert into tablexxx
> values" in parallel, this is a range partition tables, total 12
>
On Mon, May 12, 2025 at 9:07 PM Tom Lane wrote:
> Maxim Boguk writes:
> > Reading the code - probably the lowest hanging fruit is to make
> > 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> > future versions.
Is the 100x backend memory usage per cached plan differenc
On Tue, 13 May 2025 at 03:19, Maxim Boguk wrote:
> On Mon, May 12, 2025 at 6:01 PM David Rowley wrote:
>> This is just an artifact of the fact that runtime pruning is not factored
>> into the costs. Note the cost of the generic plan. The plan_cache_mode GUC
>> is about the only way to overrule
On 5/12/25 20:07, Tom Lane wrote:
Maxim Boguk writes:
Reading the code - probably the lowest hanging fruit is to make
'The current multiplier of 1000 * cpu_operator_cost' configurable in the
future versions.
I'm wondering whether we should try to make the planner not expend
the effort in the
Maxim Boguk writes:
> Reading the code - probably the lowest hanging fruit is to make
> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> future versions.
I'm wondering whether we should try to make the planner not expend
the effort in the first place, but leave partition
On Mon, May 12, 2025 at 6:33 PM 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 cost
> > of real time partition pruning of a generic plan).
>
> False. The est
On 12/5/2025 16:04, Maxim Boguk wrote:
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov It is not hard to write such a tiny extension. As I see, the only extra
stored "C" procedure is needed to set up force-plan-type flag employing
FetchPreparedStatement(). The rest of the code - query
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 cost
> of real time partition pruning of a generic plan).
False. The estimate is evidently pretty wrong, but it's not that
there is n
On Mon, May 12, 2025 at 6:01 PM David Rowley wrote:
> On Mon, 12 May 2025, 05:08 Andrei Lepikhov, wrote:
>
>> Thanks for this puzzle!
>> I suppose, in case generic planning is much faster than custom one,
>> there are two candidates exist:
>> 1. Touching the index during planning causes too much
On Mon, 12 May 2025, 05:08 Andrei Lepikhov, wrote:
> Thanks for this puzzle!
> I suppose, in case generic planning is much faster than custom one,
> there are two candidates exist:
> 1. Touching the index during planning causes too much overhead - see
> get_actual_variable_range
> 2. You have a m
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov wrote:
> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95%
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov wrote:
> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95%
On 5/12/25 15:08, Maxim Boguk wrote:
PS: problem not with difference between custom and generic planning time
but with prepared statements
generic plan plans only once, but custom plan plan every call (and plan
time cost 95% on total query runtime).
Ah, now I got it.
I'm aware of this problem f
On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov wrote:
> On 5/12/25 13:49, Maxim Boguk wrote:
> > I suspect this situation should be quite common with queries over
> > partitioned tables (where planning time is usually quite a high).
> >
> > Any suggestions what could be done there outside of usi
On 5/12/25 13:49, Maxim Boguk wrote:
I suspect this situation should be quite common with queries over
partitioned tables (where planning time is usually quite a high).
Any suggestions what could be done there outside of using
force_generic_plan for a particular db user (which will kill perfor
One additional point about reindexing I should have mentioned, make sure
you have adequate disk space to complete the reindexing.
Craig
On Mon, May 5, 2025 at 8:33 AM Craig Jackson
wrote:
> For the two indexes that take 12 hours to vacuum: If you can drop and
> rebuild them in less than the 12
For the two indexes that take 12 hours to vacuum: If you can drop and
rebuild them in less than the 12 hours it takes to vacuum them and you can
have them be offline then I would do that. If you can't take the indexes
offline then consider reindexing online.
Also, if the indexes aren't needed for
Hi
It is not your question but for such situations, you should consider using
partitioning.
And more closely to your question: I would not disable autovacuum but it must
not work with default values.
Best regards
Michel SALAIS
De : Leo
Envoyé : vendredi 2 mai 2025 16:23
À : pgsql-
On Fri, May 2, 2025 at 9:23 PM Leo wrote:
> I am purging old records from a table (500 million rows, but I am doing it in
> sets of 50,000,000 with a smaller loop of 100,000). That works just fine.
>
> Because of the amount of data/rows deleted, I disabled the autovacuum for
> this table (I w
Also, is there a way to estimate the vacuum execution? Something like
explain plan - without actually vacuuming, just to see how it will perform
it - like a degree of parallelism?
On Fri, May 2, 2025 at 10:23 AM Leo wrote:
> I have been working on AWS PostgreSQL RDS for a few years, but still n
=?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= writes:
> Thanks a lot for your response Tom.
> May I ask how do you debug those functions?
> Or is it just that you read the code and more or less guess what should be
> the value for each variable with information coming from querying Postgres
> tables?
The
Thanks a lot for your response Tom.
May I ask how do you debug those functions?
Or is it just that you read the code and more or less guess what should be
the value for each variable with information coming from querying Postgres
tables?
Thanks a lot.
El lun, 28 abr 2025 a las 17:07, Tom Lane ()
Laurenz Albe writes:
> On Mon, 2025-04-28 at 15:22 +0200, Felipe López Montes wrote:
>> Following the book PostgreSQL Query Optimization (Second Edition), there is a
>> statement on page 90 talking about Partial Indexes that says that the planner
>> will use the partial index rather than the full
Hi Mr. Laurenz,
Thanks a lot for your response :).
The full index is bigger as it has an entry for all the rows of the table,
whilst the partial one only has entries for canceled flights.
Output of pgstatindex() for the *partial index:*
version,tree_level,index_size,root_block_no,internal_pages
On Mon, 2025-04-28 at 15:22 +0200, Felipe López Montes wrote:
> I am using PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> 12.4.0, 64-bit,
> and working with the postgres_air Database.
>
> I have a very simple query (please forget about the sense of the query itself,
> I just want
On Wed, 2025-04-16 at 11:16 +, Weck, Luis wrote:
> I am not sure if this list is the most appropriate, but I figured I’d share
> it here…
>
> If a column has a check constraint, such as CHECK (length(value) < 10) or even
> something like a VARCHAR(10) shouldn’t a query like this become a no-o
15.04.2025 13:00, Andres Freund пишет:
> 1) Increasing NUM_XLOGINSERT_LOCKS allows more contention on insertpos_lck and
>spinlocks scale really badly under heavy contention
>
> I think we can redesign the mechanism so that there's an LSN ordered
> ringbuffer of in-progress insertions, with the
15.04.2025 13:53, Andres Freund пишет:
> Hi,
>
> On 2025-04-15 13:44:09 +0300, Yura Sokolov wrote:
>> 15.04.2025 13:00, Andres Freund пишет:
>>> 1) Increasing NUM_XLOGINSERT_LOCKS allows more contention on insertpos_lck
>>> and
>>>spinlocks scale really badly under heavy contention
>>>
>>> I
Hi,
On 2025-04-15 13:44:09 +0300, Yura Sokolov wrote:
> 15.04.2025 13:00, Andres Freund пишет:
> > 1) Increasing NUM_XLOGINSERT_LOCKS allows more contention on insertpos_lck
> > and
> >spinlocks scale really badly under heavy contention
> >
> > I think we can redesign the mechanism so that t
Hi,
On 2025-04-15 12:16:40 +0300, Yura Sokolov wrote:
> 11.04.2025 17:36, James Pang пишет:
> > pgv14.8 , during peak time, we suddenly see hundreds of active sessions
> > waiting on LWlock WALWrite at the same time, but we did not find any issue
> > on storage .
> > any suggestions ?
>
> No
11.04.2025 17:36, James Pang пишет:
> pgv14.8 , during peak time, we suddenly see hundreds of active sessions
> waiting on LWlock WALWrite at the same time, but we did not find any issue
> on storage .
> any suggestions ?
No real suggestions...
There is single WALWrite lock.
So only single pr
LWLock always shows up in the case where you have too many concurrent
active connections. Do a select from the pg_stat_activity table where
state in ('idle in transaction','active'); Then count how many CPUs you
have. If the sql query count returned is greater than 2-3 times the
number of CPUs, y
On Fri, 2025-04-11 at 22:36 +0800, James Pang wrote:
> pgv14.8 , during peak time, we suddenly see hundreds of active sessions
> waiting on LWlock
> WALWrite at the same time, but we did not find any issue on storage .
> any suggestions ?
You should get a reasonably sized (much smaller) connecti
You may be interested in a patch "Adding pg_dump flag for parallel
export to pipes"[1] which allows using pipes in directory former
parallel dump and restore.
There the offsets are implicitly taken care of by the file system.
[1]
https://www.postgresql.org/message-id/CAH5HC97p4kkpikar%2BswuC0Lx4Y
1 - 100 of 1785 matches
Mail list logo
