Ba Jinsheng writes:
>> 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
On Fri, 25 Oct 2024, 14:36 James Pang, wrote:
> experts,
> we faced into a lot of lwlock:LockManager wait-events , all of these
> queries are "select ..." , there are other several session are doing DML,
> insert/update/delete on same table. Did these DML transactions holding
> "transacti
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 s
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
>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 of
paths?
Best regards,
Jinsheng B
You may find this helpful, the advice is not specific to RDS.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.html
HTH.
On Fri, 25 Oct 2024 at 18:36, Laurenz Albe wrote:
> On Fri, 2024-10-25 at 14:36 +0800, James Pang wrote:
> > we faced into a lot of lwlock:
Dear PostgreSQL Community,
For the query 10 in TPC-H benchmark:
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
CUSTOMER,
ORDERS,
LINEITEM,
NA
sorry, I sent to wrong email. please ignore.
James Pang 於 2024年10月25日週五 下午3:58寫道：
> Yes, a lot new coming sessions running some "select" and sql
> parsing/planning there, including some partition tables in the query. but
> there were other sessions DML on these tables at the same time too
>
> La
Yes, a lot new coming sessions running some "select" and sql
parsing/planning there, including some partition tables in the query. but
there were other sessions DML on these tables at the same time too
Laurenz Albe 於 2024年7月19日週五 下午7:41寫道：
> On Sat, 2021-04-10 at 08:58 +0200, Pavel Stehule wrote
On Fri, 2024-10-25 at 14:36 +0800, James Pang wrote:
> we faced into a lot of lwlock:LockManager wait-events , all of these queries
> are "select ..." ,
> there are other several session are doing DML, insert/update/delete on same
> table.
> Did these DML transactions holding "transactionid" and
12 matches
Mail list logo
