pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery performance issue
14+ messages / 9 participants
[nested] [flat]
* Query performance issue
@ 2003-06-02 19:49 terry@ashtonwoodshomes.com
0 siblings, 0 replies; 14+ messages in thread
From: terry@ashtonwoodshomes.com @ 2003-06-02 19:49 UTC (permalink / raw)
To: pgsql-general
This query runs fast:
SELECT supplier_id, supplier_id AS aging__item_id, supplier_name AS
aging__item_name
FROM suppliers AS supp
WHERE supp.division_id = 'GGH'
AND supp.supplier_id IN (SELECT DISTINCT master__dt.assigned_supplier_id
FROM deficiency_table AS master__dt WHERE master__dt.deficiency_status_id IN
('LEGAL', 'OPEN', 'PENDIN', 'REOPEN') )
ORDER BY aging__item_name
NOTICE: QUERY PLAN:
Sort (cost=111725225.41..111725225.41 rows=2737 width=58)
-> Seq Scan on suppliers supp (cost=0.00..111725069.18 rows=2737
width=58)
SubPlan
-> Materialize (cost=5348.15..5348.15 rows=308 width=4)
-> Unique (cost=5340.44..5348.15 rows=308 width=4)
-> Sort (cost=5340.44..5340.44 rows=3083 width=4)
-> Seq Scan on deficiency_table master__dt
(cost=0.00..5161.78 rows=3083 width=4)
And so does the (equivalent) nested one:
SELECT count(dt.lot_id) AS def_count_inner
FROM deficiency_table AS dt
WHERE dt.deficiency_status_id IN ('LEGAL', 'OPEN', 'REOPEN')
AND dt.assigned_supplier_id = '100001'
NOTICE: QUERY PLAN:
Aggregate (cost=5161.82..5161.82 rows=1 width=4)
-> Seq Scan on deficiency_table dt (cost=0.00..5161.78 rows=14 width=4)
But when I put the nested one inside, it takes a long time to run. The
query takes a long time to run:
SELECT supplier_id, supplier_id AS aging__item_id, supplier_name AS
aging__item_name
, (SELECT count(dt.lot_id) AS def_count_inner
FROM deficiency_table AS dt
WHERE dt.deficiency_status_id IN ('LEGAL', 'OPEN', 'REOPEN')
AND dt.assigned_supplier_id = supp.supplier_id
) AS def_count
FROM suppliers AS supp
WHERE supp.division_id = 'GGH'
AND supp.supplier_id IN (SELECT DISTINCT master__dt.assigned_supplier_id
FROM deficiency_table AS master__dt WHERE master__dt.deficiency_status_id IN
('LEGAL', 'OPEN', 'PENDIN', 'REOPEN') )
ORDER BY aging__item_name
NOTICE: QUERY PLAN:
Sort (cost=111725225.41..111725225.41 rows=2737 width=58)
-> Seq Scan on suppliers supp (cost=0.00..111725069.18 rows=2737
width=58)
SubPlan
-> Aggregate (cost=5161.86..5161.86 rows=1 width=4)
-> Seq Scan on deficiency_table dt (cost=0.00..5161.78
rows=32 width=4)
-> Materialize (cost=5348.15..5348.15 rows=308 width=4)
-> Unique (cost=5340.44..5348.15 rows=308 width=4)
-> Sort (cost=5340.44..5340.44 rows=3083 width=4)
-> Seq Scan on deficiency_table master__dt
(cost=0.00..5161.78 rows=3083 width=4)
PLEASE NOTE:
I have even tried changing the outer WHERE clause from:
AND supp.supplier_id IN (SELECT DISTINCT master__dt.assigned_supplier_id
FROM deficiency_table AS master__dt WHERE master__dt.deficiency_status_id IN
('LEGAL', 'OPEN', 'PENDIN', 'REOPEN') )
To:
AND supp.supplier_id IN ('0', '100001', '100002', '100004', '100007',
'100018', '100040', '100070', '100130', '100177', '100223', '100284',
'100315', '100350', '100360', '100380', '100392', '100422', '100535',
'100927', '101311', '101359', '101420', '101665', '101686', '101690',
'101711', '101908', '101945', '102014', '102319', '102420', '102425',
'102498', '102542', '102593', '102672', '102683', '102726', '102730',
'102755', '102795', '102888', '102890', '102951', '103000', '103060',
'103163', '103349', '103350', '103361', '103512', '103526', '103694',
'103708', '103710', '103790', '103832', '103945', '103976', '104023',
'104039', '104206', '104289', '104314', '104350', '104359', '104364',
'104478', '104483', '104519', '104521', '104589', '104621', '104633',
'104636', '104642', '104646', '104659', '104662', '104669', '104705',
'104710', '104714', '104722', '104866', '104914', '105061', '105225',
'105233', '105462', '')
Which gives:
NOTICE: QUERY PLAN:
Sort (cost=519.17..519.17 rows=24 width=58)
-> Index Scan using suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey, suppliers_pkey,
suppliers
_pkey, suppliers_pkey, suppliers_pkey on suppliers supp (cost=0.00..518.63
rows
=24 width=58)
SubPlan
-> Aggregate (cost=5161.86..5161.86 rows=1 width=4)
-> Seq Scan on deficiency_table dt (cost=0.00..5161.78
rows=32 width=4)
SELECT supplier_id, supplier_id AS aging__item_id, supplier_name AS
aging__item_name
, (SELECT count(dt.lot_id) AS def_count_inner
FROM deficiency_table AS dt
WHERE dt.deficiency_status_id IN ('LEGAL', 'OPEN', 'REOPEN')
AND dt.assigned_supplier_id = supp.supplier_id
) AS def_count
FROM suppliers AS supp
WHERE supp.division_id = 'GGH'
AND supp.supplier_id IN ('100001', '100002', '100004', '100007', '100018',
'100040', '100070', '100130', '100177', '100223', '100284', '100315',
'100350', '100360', '100380', '100392', '100422', '100535', '100927',
'101311', '101359', '101420', '101665', '101686', '101690', '101711',
'101908', '101945', '102014', '102319', '102420', '102425', '102498',
'102542', '102593', '102672', '102683', '102726', '102730', '102755',
'102795', '102888', '102890', '102951', '103000', '103060', '103163',
'103349', '103350', '103361', '103512', '103526', '103694', '103708',
'103710', '103790', '103832', '103945', '103976', '104023', '104039',
'104206', '104289', '104314', '104350', '104359', '104364', '104478',
'104483', '104519', '104521', '104589', '104621', '104633', '104636',
'104642', '104646', '104659', '104662', '104669', '104705', '104710',
'104714', '104722', '104866', '104914', '105061', '105225', '105233',
'105462')
ORDER BY aging__item_name
Which is also very long to run, only a very slight time saving if any.
THE BIG PROBLEM: The above query only has 1 nested clause for the value
"def_count_inner". My real query has numerous such clauses, as it is a
breakdown report, and I need one clause for each of < 30 days, 30-60 days,
60-90, over 90, etc.
I realize that my nested query does take a 100-400 milliseconds to run, and
there are about 90 suppliers returned. So 30 second response time is not
unreasonable, but my response time seems to be almost 2 minutes.
Can anyone help me with optimizing this query?
Is there a better way to write this query?
Postgres 7.2.4 on linux.
Thanks
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
^ permalink raw reply [nested|flat] 14+ messages in thread
* Query performance issue
@ 2024-10-16 17:50 yudhi s <learnerdatabase99@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: yudhi s @ 2024-10-16 17:50 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi,
Below question got in one discussion.Appreciate any guidance on this.
Below is a query which is running for ~40 seconds. As it's a query which is
executed from UI , we were expecting it to finish in <~5 seconds. It has a
"IN" and a "NOT IN" subquery , from the execution path it seems the total
response time is mainly, to be sum of the "IN" and the "NOT IN" subquery
section. My thought was that both "IN" and "NOT IN" should be
executed/evaluated in parallel but not in serial fashion.
In the execution path below , the line number marked in *bold* are the top
lines for the IN and NOT IN subquery evaluation and they are showing
"Actual time" as Approx ~9 seconds and ~8 seconds and they seems to be
summed up and the top lines showing it to be ~19 seconds. Then onwards it
keeps on increasing with other "nested loop" joins.
*Note*:- This query is running on a MYSQL 8.0 database. So I'm wondering if
there is any mysql list similar to Oracle list , in which i can share this
issue?
Added the query in below path:-
https://gist.github.com/databasetech0073/95bce00c3a6bd4ae8d195401e0383185
SELECT ......
FROM R_CON_ESTS RC_STS,
R_CON rc,
D_LKP D_LKP_STS,
D_LKP D_LKP_FRQ,
(select RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_VER_NB
from R_CON_E RCE
where RCE.MTNE_ID in (SELECT MI1.MTNE_ID
FROM M_INF mi1 WHERE MI1.AID = :AID
UNION
SELECT rg.RG_MF_SK_ID
from RG_M_F_INF rg where rg.AS_ID =:AID
UNION
SELECT fti.FT_SRK_ID
from M_FT fti where fti.AS_ID= :AID
)
and (RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_ver_nb) NOT IN
(SELECT RCE_NS.DRV_DT, RCE_NS.AID, RCE_NS.R_CON_ID, RCE_NS.R_CON_VER_NB
FROM R_CON_E RCE_NS
WHERE RCE_NS.MTNE_ID NOT IN (select MI2.MTNE_ID
from M_INF MI2 where MI2.AID = :AID
UNION
SELECT RG2.RG_MF_SK_ID
from RG_M_F_INF RG2 where RG2.AS_ID =:AID
UNION
SELECT FTI1.FT_SRK_ID
from M_FT FTI1 where FTI1.AS_ID= :AID
))
) b
where RC_STS.RR_FRQ_NB = D_LKP_FRQ.D_LKP_NB
and RC_STS.R_CON_ESTS_NB = D_LKP_STS.D_LKP_NB
and RC_STS.R_CON_ID = rc.R_CON_ID
and RC_STS.R_CON_VER_NB = rc.R_CON_VER_NB
and RC_STS.AID = rc.AID
and RC_STS.AID = b.AID
and RC_STS.R_CON_ID = b.R_CON_ID
and RC_STS.R_CON_VER_NB = b.R_CON_VER_NB
order by 3,4,2;
-> Sort: RC_STS.R_CON_ID, RC_STS.R_CON_VER_NB, RC_STS.R_EX_RID (actual
time=44392.655..44644.844 rows=745483 loops=1)
-> Stream results (cost=311479029610.37 rows=860847650219) (actual
time=8957.556..42133.969 rows=745483 loops=1)
-> Nested loop inner join (cost=311479029610.37 rows=860847650219)
(actual time=8957.548..40891.903 rows=745483 loops=1)
-> Nested loop inner join (cost=225393084569.25
rows=860847650219) (actual time=8957.541..40597.741 rows=745483 loops=1)
-> Nested loop inner join (cost=139307139528.12
rows=860847650219) (actual time=8957.530..40092.267 rows=745483 loops=1)
-> Nested loop antijoin (cost=53221194487.00
rows=532199430400) (actual time=8957.477..29529.382 rows=671352 loops=1)
* -> Nested loop inner join (cost=886687.00
rows=729520) (actual time=0.123..19714.306 rows=692583 loops=1)
-> Filter: <in_optimizer>(RCE.MTNE_ID,<exists>(select #3))
(cost=84215.00 rows=729520) (actual time=0.085..9045.124 rows=692583
loops=1)* -> Covering index scan on RCE
using R_58 (cost=84215.00 rows=729520) (actual time=0.055..534.110
rows=742706 loops=1)
-> Select #3 (subquery in condition;
dependent)
-> Limit: 1 row(s) (cost=4.41..4.41
rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union temporary>
(cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Union materialize with
deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1
loops=742706)
-> Limit table size: 1
unique row(s)
-> Limit: 1 row(s)
(cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Covering index
lookup on mi1 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX',
MTNE_ID=<cache>(RCE.MTNE_ID)) (cost=1.13 rows=1) (actual time=0.006..0.006
rows=1 loops=742706)
-> Limit table size: 1
unique row(s)
-> Limit: 1 row(s)
(cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Single-row
covering index lookup on rg using PRIMARY
(RG_MF_SK_ID=<cache>(RCE.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX') (cost=1.10
rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table size: 1
unique row(s)
-> Limit: 1 row(s)
(cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter:
(fti.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual
time=0.003..0.003 rows=0 loops=50123)
-> Covering
index lookup on fti using AK_MFTI (FT_SRK_ID=<cache>(RCE.MTNE_ID))
(cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on rc using R_26 (AID=RCE.AID,
R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.00 rows=1)
(actual time=0.014..0.015 rows=1 loops=692583)
-> Single-row index lookup on <subquery7> using
<auto_distinct_key> (DRV_DT=RCE.DRV_DT, AID=RCE.AID, R_CON_ID=RCE.R_CON_ID,
R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=157167.31..157167.31 rows=1) (actual
time=0.014..0.014 rows=0 loops=692583)
-> Materialize with deduplication
(cost=157167.00..157167.00 rows=729520) (actual time=8957.347..8957.347
rows=25843 loops=1)
* -> Filter: ((RCE_NS.DRV_DT is not null)
and (RCE_NS.AID is not null) and (RCE_NS.R_CON_ID is not null) and
(RCE_NS.R_CON_VER_NB is not null)) (cost=84215.00 rows=729520) (actual
time=1737.420..8871.505 rows=50123 loops=1)
-> Filter: <in_optimizer>(RCE_NS.MTNE_ID,<exists>(select #8) is false)
(cost=84215.00 rows=729520) (actual time=1737.417..8860.489 rows=50123
loops=1)* -> Covering index scan on
RCE_NS using R_58 (cost=84215.00 rows=729520) (actual time=0.039..531.571
rows=742706 loops=1)
-> Select #8 (subquery in
condition; dependent)
-> Limit: 1 row(s)
(cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union
temporary> (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1
loops=742706)
-> Union materialize
with deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010
rows=1 loops=742706)
-> Limit table
size: 1 unique row(s)
-> Limit: 1 row(s)
(cost=1.13 rows=1) (actual time=0.007..0.007 rows=1 loops=742706)
-> Covering
index lookup on MI2 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX',
MTNE_ID=<cache>(RCE_NS.MTNE_ID)) (cost=1.13 rows=1) (actual
time=0.006..0.006 rows=1 loops=742706)
-> Limit table
size: 1 unique row(s)
-> Limit: 1
row(s) (cost=1.10 rows=1) (actual time=0.004..0.004 rows=1 loops=132294)
->
Single-row covering index lookup on RG2 using PRIMARY
(RG_MF_SK_ID=<cache>(RCE_NS.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX')
(cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table
size: 1 unique row(s)
-> Limit: 1
row(s) (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter:
(FTI1.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual
time=0.003..0.003 rows=0 loops=50123)
->
Covering index lookup on FTI1 using AK_MFTI
(FT_SRK_ID=<cache>(RCE_NS.MTNE_ID)) (cost=0.74 rows=2) (actual
time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on RC_STS using RCE_STS (AID=RCE.AID,
R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.62 rows=2)
(actual time=0.013..0.016 rows=1 loops=671352)
-> Single-row index lookup on D_LKP_STS using PRIMARY
(D_LKP_NB=RC_STS.R_CON_ESTS_NB) (cost=1.00 rows=1) (actual
time=0.000..0.000 rows=1 loops=745483)
-> Single-row index lookup on D_LKP_FRQ using PRIMARY
(D_LKP_NB=RC_STS.RR_FRQ_NB) (cost=1.00 rows=1) (actual time=0.000..0.000
rows=1 loops=745483)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-16 18:03 Adrian Klaver <adrian.klaver@aklaver.com>
parent: yudhi s <learnerdatabase99@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Adrian Klaver @ 2024-10-16 18:03 UTC (permalink / raw)
To: yudhi s <learnerdatabase99@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 10/16/24 10:50 AM, yudhi s wrote:
> Hi,
>
> Below question got in one discussion.Appreciate any guidance on this.
>
> Below is a query which is running for ~40 seconds. As it's a query which
> is executed from UI , we were expecting it to finish in <~5 seconds. It
> has a "IN" and a "NOT IN" subquery , from the execution path it seems
> the total response time is mainly, to be sum of the "IN" and the "NOT
> IN" subquery section. My thought was that both "IN" and "NOT IN" should
> be executed/evaluated in parallel but not in serial fashion.
>
> In the execution path below , the line number marked in *bold* are the
> top lines for the IN and NOT IN subquery evaluation and they are showing
> "Actual time" as Approx ~9 seconds and ~8 seconds and they seems to be
> summed up and the top lines showing it to be ~19 seconds. Then onwards
> it keeps on increasing with other "nested loop" joins.
>
> *Note*:- This query is running on a MYSQL 8.0 database. So I'm wondering
> if there is any mysql list similar to Oracle list , in which i can share
> this issue?
>
This is the Postgres list.
As to your question maybe one of the forums?:
https://forums.mysql.com/
In particular Performance:
https://forums.mysql.com/list.php?24
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-16 21:36 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: yudhi s <learnerdatabase99@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: Peter J. Holzer @ 2024-10-16 21:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> Below is a query which is running for ~40 seconds.
[...]
> In the execution path below , the line number marked in bold are the top lines
> for the IN and NOT IN subquery evaluation and they are showing "Actual time" as
> Approx ~9 seconds and ~8 seconds and they seems to be summed up and the top
> lines showing it to be ~19 seconds. Then onwards it keeps on increasing with
> other "nested loop" joins.
>
> Note:- This query is running on a MYSQL 8.0 database. So I'm wondering if there
> is any mysql list similar to Oracle list , in which i can share this issue?
The execution plan looks like a postgresql execution plan, not a mysql
execution plan. Did you run this query on postgresql? That may be
interesting for comparison purposese, but ultimately it is useless: You
won't get mysql to work like postgresql, and any tips to speed up this
query on postgresql (which is all you can expect on a postgresql mailing
list) probably won't work on mysql.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-17 06:27 yudhi s <learnerdatabase99@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 0 replies; 14+ messages in thread
From: yudhi s @ 2024-10-17 06:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> > Below is a query which is running for ~40 seconds.
> [...]
> > In the execution path below , the line number marked in bold are the top
> lines
> > for the IN and NOT IN subquery evaluation and they are showing "Actual
> time" as
> > Approx ~9 seconds and ~8 seconds and they seems to be summed up and the
> top
> > lines showing it to be ~19 seconds. Then onwards it keeps on increasing
> with
> > other "nested loop" joins.
> >
> > Note:- This query is running on a MYSQL 8.0 database. So I'm wondering
> if there
> > is any mysql list similar to Oracle list , in which i can share this
> issue?
>
> The execution plan looks like a postgresql execution plan, not a mysql
> execution plan. Did you run this query on postgresql? That may be
> interesting for comparison purposese, but ultimately it is useless: You
> won't get mysql to work like postgresql, and any tips to speed up this
> query on postgresql (which is all you can expect on a postgresql mailing
> list) probably won't work on mysql.
>
>
>
Agreed. Postgres and mysql may have differences in how the optimizer is
interpreting the stats and coming up with the execution oath. However, I
was looking if the query can be written efficiently by tweaking the current
logic. It's actually spending the majority of the time doing the "IN" and
"NOT IN" evaluation and in that it's using the same exact subquery for the
"UNION ALL" . And the overall execution time is summation of the IN and NOT
IN clause evaluation. So I was thinking of a better way of writing the
same logically.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 05:31 yudhi s <learnerdatabase99@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 2 replies; 14+ messages in thread
From: yudhi s @ 2024-10-22 05:31 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; hjp-pgsql@hjp.at
On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> The execution plan looks like a postgresql execution plan, not a mysql
> execution plan. Did you run this query on postgresql? That may be
> interesting for comparison purposese, but ultimately it is useless: You
> won't get mysql to work like postgresql, and any tips to speed up this
> query on postgresql (which is all you can expect on a postgresql mailing
> list) probably won't work on mysql.
>
>
>
Tried running the same in postgres and below is the plan from bothe
postgres and mysql. Can you please guide me to understand , if anything
else can be done to make it better?
https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 16:00 Jeff Ross <jross@openvistas.net>
parent: yudhi s <learnerdatabase99@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Jeff Ross @ 2024-10-22 16:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 10/21/24 23:31, yudhi s wrote:
>
> On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
>
> The execution plan looks like a postgresql execution plan, not a mysql
> execution plan. Did you run this query on postgresql? That may be
> interesting for comparison purposese, but ultimately it is
> useless: You
> won't get mysql to work like postgresql, and any tips to speed up this
> query on postgresql (which is all you can expect on a postgresql
> mailing
> list) probably won't work on mysql.
>
>
>
> Tried running the same in postgres and below is the plan from bothe
> postgres and mysql. Can you please guide me to understand , if
> anything else can be done to make it better?
>
> https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8
>
Here's your postgres query as analyzed and made more readable by the
most excellent explain.depesz.com.
https://explain.depesz.com/s/VyeM#html
Under the hints tab are suggestions to bump your work_mem to avoid
writing sorts out to disk.
Jeff
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 19:01 Greg Sabino Mullane <htamfids@gmail.com>
parent: yudhi s <learnerdatabase99@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: Greg Sabino Mullane @ 2024-10-22 19:01 UTC (permalink / raw)
To: yudhi s <learnerdatabase99@gmail.com>; +Cc: pgsql-general@lists.postgresql.org; hjp-pgsql@hjp.at
To be frank, there is so much wrong with this query that it is hard to know
where to start. But a few top items:
* Make sure all of the tables involved have been analyzed. You might want
to bump default_statistics_target up and see if that helps.
* As mentioned already, increase work_mem, as you have things spilling to
disk (e.g. external merge Disk: 36280kB)
* Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
JOIN table2 ON (...) JOIN table3 ON (...)
* Try not to use subselects. Things like WHERE x IN (SELECT ...) are
expensive and hard to optimize.
* You have useless GROUP BY clauses in there. Remove to simplify the query
* There is no LIMIT. Does the client really need all 135,214 rows?
Cheers,
Greg
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 20:00 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Greg Sabino Mullane <htamfids@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2024-10-22 20:00 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
[snip]
> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
> JOIN table2 ON (...) JOIN table3 ON (...)
>
Why?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 20:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2024-10-22 20:06 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane <htamfids@gmail.com>
> wrote:
> [snip]
>
>> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
>> JOIN table2 ON (...) JOIN table3 ON (...)
>>
>
> Why?
>
>
Readability is improved when done correctly; and you need to know it for
outer joins anyway, so consistency.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 20:36 yudhi s <learnerdatabase99@gmail.com>
parent: Greg Sabino Mullane <htamfids@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: yudhi s @ 2024-10-22 20:36 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; jross@openvistas.net; +Cc: pgsql-general@lists.postgresql.org; hjp-pgsql@hjp.at
On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> To be frank, there is so much wrong with this query that it is hard to
> know where to start. But a few top items:
>
> * Make sure all of the tables involved have been analyzed. You might want
> to bump default_statistics_target up and see if that helps.
>
> * As mentioned already, increase work_mem, as you have things spilling to
> disk (e.g. external merge Disk: 36280kB)
>
> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
> JOIN table2 ON (...) JOIN table3 ON (...)
>
> * Try not to use subselects. Things like WHERE x IN (SELECT ...) are
> expensive and hard to optimize.
>
> * You have useless GROUP BY clauses in there. Remove to simplify the query
>
> * There is no LIMIT. Does the client really need all 135,214 rows?
>
>
>
I tried running the query by removing both the "group by" from the inner
subqueries (I think the initial thought was that they will give distinct
records to the outer query and will thus help), and added limit 500 at the
last and also set the work_mem to 2GB for that session before running the
query. But seeing the response increased to ~5 seconds (from ~3.1 seconds
earlier). Below I have updated the execution plan for the same at the last
section.
https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8
Again , not able to clearly understand the third point you said below. Can
you please clarify a bit more. Do you mean we should write it as exists
/not exists rather IN and NOT IN and that will improve the performance? I
hope the third point doesn't matter much as we have all equijoin used here.
Correct me if I'm wrong.
*"Try not to use subselects. Things like WHERE x IN (SELECT ...) are
expensive and hard to optimize*."
Additionally in the plan which mysql makes and showing the highest response
time, is it suffering because of differences of the speed of the underlying
IO/storage or is it just because of the optimization features which are
available in postgres and not there in mysql ? Trying to understand if it
can be identified from the execution plan itself.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 20:47 Laurenz Albe <laurenz.albe@cybertec.at>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2024-10-22 20:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, 2024-10-22 at 13:06 -0700, David G. Johnston wrote:
> On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> > On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
> > > * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1 JOIN table2 ON (...) JOIN table3 ON (...)
> >
> > Why?
>
> Readability is improved when done correctly; and you need to know it for outer joins anyway, so consistency.
Also, you cannot accidentally forget a join condition.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-22 21:23 yudhi s <learnerdatabase99@gmail.com>
parent: yudhi s <learnerdatabase99@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: yudhi s @ 2024-10-22 21:23 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; jross@openvistas.net; +Cc: pgsql-general@lists.postgresql.org; hjp-pgsql@hjp.at
On Wed, Oct 23, 2024 at 2:06 AM yudhi s <learnerdatabase99@gmail.com> wrote:
>
>
> On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane <htamfids@gmail.com>
> wrote:
>
>> To be frank, there is so much wrong with this query that it is hard to
>> know where to start. But a few top items:
>>
>> * Make sure all of the tables involved have been analyzed. You might want
>> to bump default_statistics_target up and see if that helps.
>>
>> * As mentioned already, increase work_mem, as you have things spilling to
>> disk (e.g. external merge Disk: 36280kB)
>>
>> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
>> JOIN table2 ON (...) JOIN table3 ON (...)
>>
>> * Try not to use subselects. Things like WHERE x IN (SELECT ...) are
>> expensive and hard to optimize.
>>
>> * You have useless GROUP BY clauses in there. Remove to simplify the query
>>
>> * There is no LIMIT. Does the client really need all 135,214 rows?
>>
>>
>>
> I tried running the query by removing both the "group by" from the inner
> subqueries (I think the initial thought was that they will give distinct
> records to the outer query and will thus help), and added limit 500 at the
> last and also set the work_mem to 2GB for that session before running the
> query. But seeing the response increased to ~5 seconds (from ~3.1 seconds
> earlier). Below I have updated the execution plan for the same at the last
> section.
>
> https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8
>
> Again , not able to clearly understand the third point you said below. Can
> you please clarify a bit more. Do you mean we should write it as exists
> /not exists rather IN and NOT IN and that will improve the performance? I
> hope the third point doesn't matter much as we have all equijoin used here.
> Correct me if I'm wrong.
>
> *"Try not to use subselects. Things like WHERE x IN (SELECT ...) are
> expensive and hard to optimize*."
>
> Additionally in the plan which mysql makes and showing the highest
> response time, is it suffering because of differences of the speed of the
> underlying IO/storage or is it just because of the optimization features
> which are available in postgres and not there in mysql ? Trying to
> understand if it can be identified from the execution plan itself.
>
Additionally, I see below differences, when I compare two specific lines of
the plan which fetches data using the same index in both posgres and mysql,
so does it point that the speed in mysql is slower as compared to postgres.
And thus is it possible that the underlying infrastructure is playing a
role here in the mysql slowness too and that might be the key one here to
first address for mysql
OR its any optimization feature which helps postgres to give it an edge in
performance here over mysql?
In mysql plan:-
-> Index lookup on EX_STS using EX_STS_INDEX (AID=b3.AID, RC_ID=b3.RC_ID,
RC_VNB=b3.RC_VNB) (cost=0.43 rows=2) (actual time=0.014..0.021 rows=2
loops=70904)
VS
In postgres plan:-
-> Index Scan using EX_STS_INDEX on RCE_STS EX_STS (cost=0.42..0.82 rows=1
width=424) (actual time=0.006..0.007 rows=2 loops=70904)
**************
In mysql plan
-> Covering index lookup on mns using M_INF_AID_index
(AID='XXXXXXXXXXXXXXXXXXX') (cost=9187.54 rows=72748) (actual
time=0.058..19.637 rows=35980 loops=1)
-> Filter:
(RNS.ASID = 'XXXXXXXXXXXXXXXXXXX') (cost=43.50 rows=42) (actual
time=0.042..0.218 rows=97 loops=1)
VS
In postgres plan
-> Bitmap Index Scan on M_INF_AID_index (cost=0.00..406.98 rows=36074
width=0) (actual time=0.790..0.790 rows=35980 loops=1)
Index Cond: ((AID)::text =
'XXXXXXXXXXXXXXXXXXX'::text)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query performance issue
@ 2024-10-24 14:01 Greg Sabino Mullane <htamfids@gmail.com>
parent: yudhi s <learnerdatabase99@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Greg Sabino Mullane @ 2024-10-24 14:01 UTC (permalink / raw)
To: yudhi s <learnerdatabase99@gmail.com>; +Cc: jross@openvistas.net; pgsql-general@lists.postgresql.org; hjp-pgsql@hjp.at
>
> Additionally in the plan which mysql makes and showing the highest
> response time, is it suffering because of differences of the speed of the
> underlying IO/storage or is it just because of the optimization features
> which are available in postgres and not there in mysql ? Trying to
> understand if it can be identified from the execution plan itself.
>
I think trying to compare postgres and mysql plans against each other is
not a very useful endeavor. There are fundamental design decisions between
the two. Focus on making your Postgres query the best it can be, full stop.
Optimize your queries, make sure the database is analyzed, and tweak some
configs as needed.
Also, you cannot accidentally forget a join condition.
Yes, this is the primary reason. Cartesian joins hurt.
Again , not able to clearly understand the third point you said below. Can
> you please clarify a bit more. Do you mean we should write it as exists
> /not exists rather IN and NOT IN and that will improve the performance?
It gives Postgres more options on how to do things, so yes, it can be
better.
Cheers,
Greg
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-10-24 14:01 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-02 19:49 Query performance issue terry@ashtonwoodshomes.com
2024-10-16 17:50 Query performance issue yudhi s <learnerdatabase99@gmail.com>
2024-10-16 18:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-10-16 21:36 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2024-10-17 06:27 ` yudhi s <learnerdatabase99@gmail.com>
2024-10-22 05:31 ` yudhi s <learnerdatabase99@gmail.com>
2024-10-22 16:00 ` Jeff Ross <jross@openvistas.net>
2024-10-22 19:01 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-10-22 20:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-10-22 20:06 ` David G. Johnston <david.g.johnston@gmail.com>
2024-10-22 20:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-10-22 20:36 ` yudhi s <learnerdatabase99@gmail.com>
2024-10-22 21:23 ` yudhi s <learnerdatabase99@gmail.com>
2024-10-24 14:01 ` Greg Sabino Mullane <htamfids@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox