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 p
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 partia
.1 postgres -c "DROP DATABASE IF
> EXISTS spi_test WITH (FORCE);" -c "CREATE DATABASE spi_test;"
> ```
I wonder if this is related to the change to CREATE DATABASE in release 15.
Maybe you could test this with "CREATE DATABASE spi_test STRATEGY =
file_copy;" and see
unfortunately that increased the query execution time by a factor of 8:
https://explain.dalibo.com/plan/a6d2443d87fea1ee
On Tue, 1 Apr 2025 at 16:28, Tom Lane wrote:
> Chris Joysn writes:
> > I have an issue when using CTEs. A query, which consists of multiple
> CTEs,
> > runs usually rather f
> CREATE STATISTICS st_simrun_component_metadata (dependencies) ON
>> sim_run_id, key FROM sim_run_component_metadata;
>> ANALYZE sim_run_component_metadata;
>>
>> When I run this query, no statistics are returned:
>>
>> SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid =
>> stxoi
>
> CREATE STATISTICS st_simrun_component_metadata (dependencies) ON
> sim_run_id, key FROM sim_run_component_metadata;
> ANALYZE sim_run_component_metadata;
>
> When I run this query, no statistics are returned:
>
> SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid =
> stxoid),
>
query on a almost same
> set of data, quantity wise, may give indeed
> good performance when using that CTE.
> This is the slow performing query using CTE:
> https://explain.dalibo.com/plan/45ce86d9cfge14c7
> And this is the fast performing query without that CTE:
> https://exp
Chris Joysn writes:
> I have an issue when using CTEs. A query, which consists of multiple CTEs,
> runs usually rather fast (~5s on my environment). But it turned out that
> using one CTE can lead to execution times of up to one minute.
> That CTE is used two times within the query.
Try labeling
, compared to results of the other CTEs its a fraction of the data.
When replacing this CTE and use the original table instead in the jions,
the query performs nicely.
However, it is not always like this. Running the same query on a almost
same set of data, quantity wise, may give indeed good performance
Hi,
Here are some observations.
Em seg., 17 de mar. de 2025 às 09:19, escreveu:
> > PostgreSQL has a lot of overhead per row.
>
> Okay, thanks. I'm not actually too worried about this since in my
> scenario, each row is about 1.5 kB, so the % overhead is negligible.
>
> > It is probably not the
n inserting 3 million)
> Well, that is not a great statement.
>
> Understood, but I was highlighting the performance of deleting 3 million
> rows identified by 3 million IDs, as opposed to deleting rows in a given
> range of IDs or deleting the whole table. It seems like deleting 3 mi
On 2025-Mar-13, bill.po...@ymail.com wrote:
> I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on
> the data, so sadly I cannot use COPY.
>
> I have discovered that for some reason, performing the original insert
> without the ON CONFLICT statement is twice as fast as performing t
Thanks Renan! Reducing the fill factor has improved my update performance and I
am now seeing the same time for updates as inserts.
I look forward to any advancements PostgreSQL may make in the future to improve
the performance of bulk DML operations. It would be amazing if they could be
.com; pgsql-performa...@postgresql.org
Subject: Re: Bulk DML performance
On Thu, 2025-03-13 at 18:13 +0800, bill.po...@ymail.com wrote:
>
> it is noteworthy that inserting 27 MB of data into a newly created
> table creates
> 191 MB of data including the index and 127 MB of data excluding the
bles and be somewhat
> faster (5.8 seconds for the initial INSERT here).
Sadly, that is not an option for me. I'm building a production system.
> Well, that is not a great statement.
Understood, but I was highlighting the performance of deleting 3 million rows
identified by 3 million IDs
1 = 'x';
>
>
>
> If PostgreSQL is writing 191 MB on the first run and 382 MB on each
> subsequent run, then PostgreSQL is only writing about 28 MB/s. Although
> PostgreSQL is also able to write about 4.5 GB in about 35 seconds (as
> stated above), which is about 128 MB/s, so it
On Thu, 2025-03-13 at 12:28 +0100, I wrote:
> Then the best you can do is to use COPY rather than INSERT.
> It will perform better (but now vastly better).
Sorry, I meant "*not* vastly better".
Yours,
Laurenz Albe
be
> faster than updating 3 million rows (also identified by 3 million IDs).
It should be, yes.
To find out where the time is spent, use EXPLAIN (ANALYZE, BUFFERS) on the
statement.
> > To get better performance, the best I can think of is to parallelize loading
> > the data un
x'
> FROM generate_series(1, 300)
> ON CONFLICT (id) DO UPDATE
> SET text1 = 'x';
>
> If PostgreSQL is writing 191 MB on the first run and 382 MB on each subsequent
> run, then PostgreSQL is only writing about 28 MB/s. Although PostgreSQL is
> also able to writ
s (as stated
above), which is about 128 MB/s, so it seems the performance constraint
depends on the number of rows inserted more than the size of each row.
Furthermore, deleting the rows takes about 18 seconds to perform (about 4
seconds longer than the time taken to update the rows):
DELETE
Hi!
Thank everyone for Your answers!
It is now clear, that it is not PG issue and it will not be fixed
anytime soon.
I see that with pure numbers sorting en_US.utf8 is still well behind:
explain (analyze, costs, buffers, verbose)
select gen.id::text collate "C"
from generate_series(1,
On 2/28/25 17:49, Thomas Munro wrote:
On Sat, Mar 1, 2025 at 9:03 AM Joe Conway wrote:
On 2/28/25 09:16, Laurenz Albe wrote:
> On Thu, 2025-02-27 at 16:54 +0300, Alexey Borschev wrote:
>> I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.
>
> I&
On Sat, Mar 1, 2025 at 9:03 AM Joe Conway wrote:
> On 2/28/25 09:16, Laurenz Albe wrote:
> > On Thu, 2025-02-27 at 16:54 +0300, Alexey Borschev wrote:
> >> I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.
> >
> > I'd say that
On 2/28/25 09:16, Laurenz Albe wrote:
On Thu, 2025-02-27 at 16:54 +0300, Alexey Borschev wrote:
I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.
I'd say that you would have to complain to the authors of the
GNU C library, which provides this collation.
On Thu, 2025-02-27 at 16:54 +0300, Alexey Borschev wrote:
> I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.
I'd say that you would have to complain to the authors of the
GNU C library, which provides this collation.
Yours,
Laurenz Albe
Hi Alexey
On 2/27/25 15:54, Alexey Borschev wrote:
Hi everyone!
I see poor performance of text sorting of collate "en_US.utf8" in PG 17.4.
Test query:
explain (analyze, costs, buffers, verbose)
SELECT
('БвЁжЫйяЙSёYz&$' || gen.id) collate "en_US.utf8"
FRO
On Fri, 2025-02-07 at 16:16 +, Jon Emord wrote:
> explain (analyze, buffers)
> select data_model_id, primary_key
> from entity
> WHERE (data_model_id, primary_key) BETWEEN (123, ‘ABC’) AND (123, ‘DEF’)
> limit 101;
>
Jon Emord writes:
>-> Index Only Scan using entity_data_model_id_primary_key_uniq on entity
> (cost=0.70..873753.60 rows=15581254 width=31) (actual time=0.093..2712.836
> rows=100 loops=1)
> Index Cond: ((ROW(data_model_id, primary_key) >= ROW(123,
> 'ABC'::text)) AND (ROW(data_m
From: Greg Sabino Mullane
Sent: Friday, February 7, 2025 9:43 AM
To: Jon Emord
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Poor performance with row wise comparisons
You don't often get email from htamf...@gmail.com. Learn why this is
important<https://aka.ms/LearnAbou
On Fri, Feb 7, 2025 at 2:05 AM Jon Emord wrote:
> but with limit 101, the extra shared hits return
>
Can you show the explain analyze for the limit 101 case?
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
Hi everyone
I wrote a query to iterate over two different columns. These columns have a
composite unique index on them, and I’m only retrieving those columns. I found
that there was a very large difference in performance when using row wise
comparison versus a column comparison. The column
On Mon, 2025-02-03 at 15:11 +0100, Tobias Orlamünde wrote:
> We are currently in the process of upgrading from 12.15 with Timescale
> 2.11 to 17.2 with Timescale 2.17.2
> On our pre-prod env we have already upgraded and noticed a remarkable
> performance issue vs. the so far
Hi everyone.
We are currently in the process of upgrading from 12.15 with Timescale
2.11 to 17.2 with Timescale 2.17.2
On our pre-prod env we have already upgraded and noticed a remarkable
performance issue vs. the so far not upgraded production environment.
If we run the exact same query in
ly? (short of competing for system
> resources, like CPU time etc, of course)
>
> I don't believe my scenario involved a deadlock but I expect my short
> transaction was probably blocked by my long one. Does it make any sense
> that this could very significantly affect the p
t this could very significantly affect the performance of the
> non-blocked transaction?
>
> Thanks,
>
> Eric
Have you tried wait event analysis (looking at wait_event_type, wait_event,
state, query samples from pg_stat_activity)?
>
resources, like CPU time etc, of course)
I don't believe my scenario involved a deadlock but I expect my short
transaction was probably blocked by my long one. Does it make any sense
that this could very significantly affect the performance of the
non-blocked transaction?
Thanks,
Eric
Hi!
Please check the following proposal (patch in attach).
The main idea is to reject only obviously worse paths (costs considerably
more
than compared one), and to pass pre-calculated startup cost to precheck
function
for more accurate comparison.
>
> --
Regards,
Nikita Malakhov
Postgres Profess
_2
(cost=0.14..0.33 rows=1 wid>
Index Cond:
(ca_address_sk = web_sales.ws_bill_addr_sk)
Filter: (ca_gmt_offset =
'-6'::numeric)
Planning Time: 2.630 ms
Execution Time: 0.330 ms
(82
95248 width=12) (actual
> time=203.407..560.264 rows=719205 loops=1)
> The difference between both query plans is the second one uses Materialize
> instead of Memoize. From the code, it seems that changing the usage of the
> cache brings performance improvement unexpectedly.
What's
1)
Filter: (web_company_name =
'pri'::bpchar)
Rows Removed by Filter: 25
-> CTE Scan on ws_wh (cost=0.00..141904.96
rows=7095248 width=4) (actual time=0.001..25.301 r
On 22/11/2024 18:12, Ba Jinsheng wrote:
I think the key difference is that the patch disables the usage of Hash
Join, which incurs a worse performance.
Discovering your case a little more I found out the origins of the
problem: Memoize+NestLoop was not chosen because top-query LIMIT node
On 22/11/2024 18:12, Ba Jinsheng wrote:
I think the key difference is that the patch disables the usage of Hash
Join, which incurs a worse performance.
I see here a problem with a number of groups: when predicting it
incorrectly, Postgres doesn't use the Memoize node. Disabling HashJoin
Dear All
false alert, I run strace and it was obvious the slow one was producing
huge debug output, while the fast one did not. It was not even a tds_fdw
issue. It was freetds. Turned out we have forgotten enabled debugging
inside the freetds configuration. You will ask me we did I get this
e
On 11/12/24 21:37, Tom Lane wrote:
Achilleas Mantzios writes:
Our sysadm created the system debian Debian GNU/Linux 12 (bookworm)with
postgres as user 1000. Now at some point we realized that whenever we
run a pgsql cluster with another user (I found that after spending two
good days testing)
On 11/12/24 20:37, Tom Lane wrote:
> Achilleas Mantzios writes:
>> Our sysadm created the system debian Debian GNU/Linux 12 (bookworm)with
>> postgres as user 1000. Now at some point we realized that whenever we
>> run a pgsql cluster with another user (I found that after spending two
>> goo
Achilleas Mantzios writes:
> Our sysadm created the system debian Debian GNU/Linux 12 (bookworm)with
> postgres as user 1000. Now at some point we realized that whenever we
> run a pgsql cluster with another user (I found that after spending two
> good days testing), the above query runs in abo
nk we have narrowed down the problem, and this is extremely strange :
It is not a matter of pgsql version 10 or 16, it is a matter of the
postgres user, the user that owns the data dir(s) and the user of the
postgres process. We reproduced both the problem and the solution with
all combinations of versions.
To sum it up :
Our sysadm created the system debian Debian GNU/Linux 12 (bookworm)with
postgres as user 1000. Now at some point we realized that whenever we
run a pgsql cluster with another user (I found that after spending two
good days testing), the above query runs in about 1 second. With user
postgres 1000 in 30 seconds. As you saw the perf output are completely
different.
On the test VM, we removed the postgres user, recreated with uid=1003,
chown -R all the mount points + table spaces, started postgres with the
new postgres user , and bingo . The chown -R on the 5TB is instant...
just saying ...
We are puzzled what can be causing this. Tomorrow we dig into GDB ,
*trace and the like.
If this rings any bells we would be more than grateful to know.
Also, the worse, is the suspicion that maybe our whole infra performance
is affected. We hope it is only free-tds and tds_fdw .
regards
On 11/12/24 11:37, Achilleas Mantzios - cloud wrote:
>
> ...
>
> We run perf on both systems for 90 seconds to make sure that it included
> at least two runs of the slow system, and we attach both outputs. We run
> perf as :
>
> sudo perf record -g -p -- sleep 90
>
> it strikes me that we dont
to the more optimal direction.
Thanks a lot for your effort to analyze this issue, and we really
appreciate your suggestions! Currently, we focus on exposing these
issues that affect performance. In the future, we may consider to look
into such a direction as you suggested.
It would be better to pa
>> The default configurations of PostgreSQL incur the error: "ERROR: could not
>> resize shared memory segment "/PostgreSQL.3539600020" to 2097152 bytes: No
>> space left on device"
>No comment on your optimiser experiments for now, but for this error:
>it reminds me of a low/default --shm-siz
your effort to analyze this issue, and we really appreciate
your suggestions! Currently, we focus on exposing these issues that affect
performance. In the future, we may consider to look into such a direction as
you suggested.
> Have you tried any tools to improve the cardinality yet, li
On 11/11/24 02:35, Ba Jinsheng wrote:
Hi all,
Please see this case:
Query 4 on TPC-DS benchmark:
Thank you for interesting example!
Looking into explains I see two sortings:
-> Sort (cost=794037.94..794037.95 rows=1 width=132)
(actual time=3024403.310..3024403.313 rows=8 loops=1)
-> Sor
On 10.11.2024 23:16, Alena Rybakina wrote:
Hi!
On 10.11.2024 22:35, Ba Jinsheng wrote:
Hi all,
Please see this case:
Query 4 on TPC-DS benchmark:
with year_total as (
select c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c
Hi!
On 10.11.2024 22:35, Ba Jinsheng wrote:
Hi all,
Please see this case:
Query 4 on TPC-DS benchmark:
with year_total as (
select c_customer_id customer_id
,c_first_name customer_first_name
,c_last_name customer_last_name
,c_preferred_cust_flag customer_preferred_cust_
On Mon, Nov 11, 2024 at 8:36 AM Ba Jinsheng wrote:
> The default configurations of PostgreSQL incur the error: "ERROR: could not
> resize shared memory segment "/PostgreSQL.3539600020" to 2097152 bytes: No
> space left on device"
No comment on your optimiser experiments for now, but for this e
Στις 9/11/24 17:41, ο/η Tomas Vondra έγραψε:
On 11/9/24 15:05, Achilleas Mantzios wrote:
Στις 9/11/24 12:49, ο/η Tomas Vondra έγραψε:
On 11/8/24 20:32, Achilleas Mantzios wrote:
Dear All,
we have hit a serious performance regression going from 10.23 → 16.4 as
far as tds_fdw (MS SQL) FDW
On 11/9/24 15:05, Achilleas Mantzios wrote:
>
> Στις 9/11/24 12:49, ο/η Tomas Vondra έγραψε:
>> On 11/8/24 20:32, Achilleas Mantzios wrote:
>>> Dear All,
>>>
>>> we have hit a serious performance regression going from 10.23 → 16.4 as
>>> far
Στις 9/11/24 16:05, ο/η Achilleas Mantzios έγραψε:
Στις 9/11/24 12:49, ο/η Tomas Vondra έγραψε:
On 11/8/24 20:32, Achilleas Mantzios wrote:
Dear All,
we have hit a serious performance regression going from 10.23 → 16.4 as
far as tds_fdw (MS SQL) FDW is concerned. To cut the long story
Στις 9/11/24 12:49, ο/η Tomas Vondra έγραψε:
On 11/8/24 20:32, Achilleas Mantzios wrote:
Dear All,
we have hit a serious performance regression going from 10.23 → 16.4 as
far as tds_fdw (MS SQL) FDW is concerned. To cut the long story short, I
recreated the good fast “old” (pgsql 10) setup
On 11/8/24 20:32, Achilleas Mantzios wrote:
> Dear All,
>
> we have hit a serious performance regression going from 10.23 → 16.4 as
> far as tds_fdw (MS SQL) FDW is concerned. To cut the long story short, I
> recreated the good fast “old” (pgsql 10) setup on the same vm as
On 9/11/2024 03:32, Ed Sabol wrote:
CREATE STATISTICS tablename_stats_rt_nd (ndistinct) ON relation, type FROM
tablename;
The only one thing I want to note. It is enough to execute:
CREATE STATISTICS tablename_stats (mcv,ndistinct,dependencies)
ON relation, type FROM tablename;
And all the
Dear All,
we have hit a serious performance regression going from 10.23 → 16.4 as
far as tds_fdw (MS SQL) FDW is concerned. To cut the long story short, I
recreated the good fast “old” (pgsql 10) setup on the same vm as the
slow “new” (pgsql 16). Here is the bug report on github :
https
Index Cond: (type = 'document'::text)
Buffers: shared hit=4
-> Index Scan using tablename_n on tablename c (cost=0.42..18.88
rows=1 width=22) (actual time=0.048..0.115 rows=1 loops=1)
Index Cond: (name = a.name)
On Nov 7, 2024, at 9:27 PM, Andrei Lepikhov wrote:
> Postgres didn't want Materialize in this example because of the low
> estimation on its outer subquery. AFAIC, by increasing the *_page_cost's
> value, you added extra weight to the inner subquery and shifted the decision
> to use materialisa
On 11/8/24 09:45, Ed Sabol wrote:
On Nov 7, 2024, at 9:27 PM, Andrei Lepikhov wrote:
Postgres didn't want Materialize in this example because of the low estimation on its outer subquery. AFAIC, by increasing the *_page_cost's value, you added extra weight to the inner subquery
What kind of ext
On 11/8/24 08:21, Ed Sabol wrote:
On Nov 7, 2024, at 5:18 PM, David Rowley wrote:
It's impossible to say with the given information. You didn't mention
which version you upgraded from to start with.
Sorry, 15.6 to 15.7 to 15.8, but we weren't on 15.7 for very long before 15.8.
You can set r
On Nov 7, 2024, at 5:18 PM, David Rowley wrote:
> It's impossible to say with the given information. You didn't mention
> which version you upgraded from to start with.
Sorry, 15.6 to 15.7 to 15.8, but we weren't on 15.7 for very long before 15.8.
> You can set random_page_cost for just the sess
On Fri, 8 Nov 2024 at 10:54, Ed Sabol wrote:
> The good news is that, after some research and experimentation, I was able to
> fix this performance degradation by setting random_page_cost = 2.0. We've
> always used the default values for seq_page_cost and random_page_cost (
;s queries
suffered major performance degradation. This query "suddenly" went from taking
40-50 milliseconds to ~9 seconds, approximately 180-200 times longer. Vacuuming
and analyzing the table didn't help.
The good news is that, after some research and experimentation, I was able
On 11/4/24 15:42, Ba Jinsheng wrote:
The estimated cost is reduced by 90%, and the execution time is reduced
by 68%. The second query plan includes the operation Memoize, while the
first query plan does not. I am wondering if we can optimize the logic
anywhere to enable the second query plan.
Please see this case:
TPC-H query 2:
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
PART,
SUPPLIER,
PARTSUPP,
NATION,
REGION
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size
sheng Ba
From: Tom Lane
Sent: Saturday, October 26, 2024 8:28 AM
To: Ba Jinsheng
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Unexpected Performance for the Function simplify_function
- External Email -
Ba Jinsheng writes:
>> It looks like the better p
it's hard to say
>> more than that.
> I used the default configuration in the file
> src/backend/utils/misc/postgresql.conf.sample
> So the random_page_cost = 4.0
You're still admitting to nothing as to the hardware you are running
this test on.
However, 4.0 is a numbe
Hello,
The query plans and results you shared illustrate the unexpected
performance differences between using and bypassing the simplify_function()
logic in PostgreSQL’s optimizer. Here’s an in-depth analysis and thoughts
on optimizing this scenario:
*Overview of the Problem*
The purpose of
On Fri, 25 Oct 2024 at 22:26, Ba Jinsheng wrote:
> I guess the better query plan is not considered when comparing the cost of
> paths?
You might want to change effective_cache_size is set high enough.
Something like 50-75% of RAM is likely fine.
David
On 10/25/24 16:26, Ba Jinsheng wrote:
>So, I wonder if you could analyse the path-choosing logic, determine the
costs of competing paths, and explain why NestLoop wasn't chosen.
To be honest, it is a bit challenging for me.
I guess the better query plan is not considered when comparing the cost
>It looks like the better plan involves a
>nestloop with inner indexscan on lineitem, which is something whose
>estimated cost depends enormously on random_page_cost. You've given
>us exactly zero detail about your test conditions, so it's hard to say
>more than that.
I used the default configura
Jinsheng Ba
From: Andrei Lepikhov
Sent: Friday, October 25, 2024 4:13 AM
To: Ba Jinsheng
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Unexpected Performance for the Function simplify_function
- External Email -
On 10/25/24 02:43, Ba
d by 39.69%, and the execution time is reduced by
32.54%. I measured the execution time on average of 10 executions.
I am not proposing a fixing patch, as the patch is incorrect. Instead, I just
want to show disabling the simplify_function() function brings performance
benefit, and
On Thursday, October 24, 2024, Tom Lane wrote:
> Shiv Iyer writes:
> > The query plans and results you shared illustrate the unexpected
> > performance differences between using and bypassing the
> simplify_function()
> > logic in PostgreSQL’s optimizer. Here’s an in-dep
On 10/25/24 02:43, Ba Jinsheng wrote:
I am not proposing a fixing patch, as the patch is incorrect. Instead, I
just want to show disabling the simplify_function() function brings
performance benefit, and it seems unexpected. I am wondering whether we
can optimize simplify_function() to make
Ba Jinsheng writes:
> I am not proposing a fixing patch, as the patch is incorrect. Instead, I just
> want to show disabling the simplify_function() function brings performance
> benefit, and it seems unexpected. I am wondering whether we can optimize
> simplify_function()
Shiv Iyer writes:
> The query plans and results you shared illustrate the unexpected
> performance differences between using and bypassing the simplify_function()
> logic in PostgreSQL’s optimizer. Here’s an in-depth analysis and thoughts
> on optimizing this scenario:
Just out of cu
On Thu, Oct 24, 2024 at 3:49 PM Shiv Iyer wrote:
> Hello,
>
>
> The query plans and results you shared illustrate the unexpected
> performance differences between using and bypassing the
> simplify_function() logic in PostgreSQL’s optimizer. Here’s an in-depth
> ana
st comment back in 2019:
Carlos O'Donell 2019-05-09 20:44:56 UTC
> Hello. Is there any chance that the issues will be fixed? Unfortunately
> PostgreSQL Is unable to use ICU for some basic features (e.g., in the
> analyze operation).
"We haven't had anyone working on strc
On 10/6/24 14:13, Tom Lane wrote:
Joe Conway writes:
This is not surprising. There is a performance regression that started
in glibc 2.21 with regard to sorting unicode. Test with RHEL 7.x (glibc
2.17) and I bet you will see comparable results to ICU. The best answer
in the long term, IMHO
On Mon, Oct 7, 2024 at 9:02 AM Shiv Iyer wrote:
>- As the string length increases, the performance degrades exponentially
> when using special characters. This is due to the collation’s computational
> complexity for each additional character comparison.
That's a pret
Hi Andrey,
I have tried my best to answer your queries below:
### Performance Degradation with Special Characters in PostgreSQL
**Explanation**:
The performance degradation you're experiencing when using special
characters like `<`, `@`, `#`, etc., is likely due to how PostgreSQL
Joe Conway writes:
> This is not surprising. There is a performance regression that started
> in glibc 2.21 with regard to sorting unicode. Test with RHEL 7.x (glibc
> 2.17) and I bet you will see comparable results to ICU. The best answer
> in the long term, IMHO, is likely to
On 10/6/24 13:28, Andrey Stikheev wrote:
Thanks for your feedback. After looking into it further, it seems the
performance issue is indeed related to the default collation settings,
particularly when handling certain special characters like |<| in the
glibc |strcoll_l| function. This
Hi, Tom!
Thanks for your feedback. After looking into it further, it seems the
performance issue is indeed related to the default collation settings,
particularly when handling certain special characters like < in the glibc
strcoll_l function. This was confirmed during my testing on Debian
Andrey Stikheev writes:
>- Changing the collation to 'C' in the query significantly improves
>performance.
What collation are you using, pray tell? (And what database encoding?)
>- Is this performance degradation expected due to collation handling of
>cert
Dear PostgreSQL Community,
I am facing significant performance issues when executing queries that
involve string comparisons with special characters, such as <, #, !, @,
etc., especially when dealing with long strings. The query execution time
increases drastically when these characters are u
> On Aug 22, 2024, at 8:05 PM, Tom Lane wrote:
>
> Sbob writes:
>> 29 million of the 32 million rows in the table have NULL for contract_date
>
> [ blink... ] So your query is selecting at least 29/32nds of the
> table, plus however much matches the contract_date > '2022-01-01'
> alternative
Sbob writes:
> 29 million of the 32 million rows in the table have NULL for contract_date
[ blink... ] So your query is selecting at least 29/32nds of the
table, plus however much matches the contract_date > '2022-01-01'
alternative. I'm not sure how you expect that to be significantly
cheaper
t 600ms
>
> If i leave the where clause off entirely it performs table scans of the
> partitions and takes approx 18 seconds to run
>
> I am trying to get the performance to less than 2sec,
> I have tried adding indexes on the table and all partitions like this:
> CREATE INDEX ON tab
titions and takes approx 18 seconds to run
I am trying to get the performance to less than 2sec,
I have tried adding indexes on the table and all partitions like this:
CREATE INDEX ON table (contract_date NULLS FIRST) ;
but the performance with the full where clause is the same:
W
nse time for some other session, unless
you are running the server on seriously underpowered hardware.
It could be that you've misinterpreted your data, and what is actually
happening is that that other session has completed its lookup query
and is now doing fast-path large object reads and w
1 - 100 of 1096 matches
Mail list logo
