pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17059: postgresql 13 version problem related to query.
10+ messages / 5 participants
[nested] [flat]
* BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 18:31 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: PG Bug reporting form @ 2021-06-15 18:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ram.maurya@lavainternational.in
The following bug has been logged on the website:
Bug reference: 17059
Logged by: Ram Pratatp maurya
Email address: ram.maurya@lavainternational.in
PostgreSQL version: 13.0
Operating system: RHEL 8.3
Description:
Hi team,
I am facing problem related to query.
I have two DB server one is running on postgres12 (RHEL 6) and second is
running on postgresql -13 (RHEL 8.3).
Server H/W configuration is same and postgresql.conf file configuration
parameter is also same.
When I am running below queary on server postgresql-12 data comes within 2
min and when I run this quear on postgresql-13 server its take 30 min
display result.
Can you please suggest any problem in postgresql-13.
Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
postgresql-12 is 350 GB.
.........................................................................................................................................................................................................
select
DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no,
CASE WHEN "Order"."order_status"='dispatched' then 3
WHEN "Order"."order_status"='partially dispatched' then 3
WHEN "Order"."order_status"='acknowledgement pending' then 4
WHEN "Order"."order_status"='delivered' then 2
WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
"Order__actionable"
from torder "Order"
join tretailer_mst as RetailerList on
"Order".retailer_code=RetailerList.retailer_code
join torder_delivery_note as OrderDeliveryNote on
"Order".order_no=OrderDeliveryNote.order_no
left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
leave.date='2021-06-15' and leave.role='DB'
join tuser_mst DSE_user on
OrderDeliveryNote.assigned_to_username=DSE_user.username
where dbr_code='304717' AND (order_status IN (('delivered'),('dn
pending'),('acknowledgement pending'),
('partially delivered')) or ((order_status = 'dispatched' or order_status =
'partially dispatched') and leave.date = '2021-06-15'))
group by "Order".order_no
,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no
order by OrderDeliveryNote.date desc limit 50;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 18:57 Pavel Stehule <pavel.stehule@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Pavel Stehule @ 2021-06-15 18:57 UTC (permalink / raw)
To: ram.maurya@lavainternational.in, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <
noreply@postgresql.org> napsal:
> The following bug has been logged on the website:
>
> Bug reference: 17059
> Logged by: Ram Pratatp maurya
> Email address: ram.maurya@lavainternational.in
> PostgreSQL version: 13.0
> Operating system: RHEL 8.3
> Description:
>
> Hi team,
>
> I am facing problem related to query.
>
> I have two DB server one is running on postgres12 (RHEL 6) and second is
> running on postgresql -13 (RHEL 8.3).
> Server H/W configuration is same and postgresql.conf file configuration
> parameter is also same.
>
> When I am running below queary on server postgresql-12 data comes within 2
> min and when I run this quear on postgresql-13 server its take 30 min
> display result.
>
> Can you please suggest any problem in postgresql-13.
>
> Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
> postgresql-12 is 350 GB.
>
>
> .........................................................................................................................................................................................................
> select
>
> DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no,
> CASE WHEN "Order"."order_status"='dispatched' then 3
> WHEN "Order"."order_status"='partially dispatched' then 3
> WHEN "Order"."order_status"='acknowledgement pending' then 4
> WHEN "Order"."order_status"='delivered' then 2
> WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
> "Order__actionable"
> from torder "Order"
> join tretailer_mst as RetailerList on
> "Order".retailer_code=RetailerList.retailer_code
> join torder_delivery_note as OrderDeliveryNote on
> "Order".order_no=OrderDeliveryNote.order_no
> left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
> leave.date='2021-06-15' and leave.role='DB'
> join tuser_mst DSE_user on
> OrderDeliveryNote.assigned_to_username=DSE_user.username
> where dbr_code='304717' AND (order_status IN (('delivered'),('dn
> pending'),('acknowledgement pending'),
> ('partially delivered')) or ((order_status = 'dispatched' or order_status =
> 'partially dispatched') and leave.date = '2021-06-15'))
> group by "Order".order_no
>
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
> order by OrderDeliveryNote.date desc limit 50;
>
please, send result of explain analyze for pg 12 and pg 13
https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://explain.depesz.com/
Regards
Pavel
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 19:36 Ram Pratap Maurya <ram.maurya@lavainternational.in>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ram Pratap Maurya @ 2021-06-15 19:36 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Dear Team,
Please find EXPLAIN ANALYZE details of Query:
Postgresql-12 :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=87069.03..87069.21 rows=5 width=346) (actual time=1202.073..1202.090 rows=5 loops=1)
-> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual time=1202.071..1202.086 rows=5 loops=1)
-> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual time=1202.070..1202.070 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 7922kB
-> HashAggregate (cost=84634.67..85207.16 rows=25444 width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Merge Join (cost=77821.29..83807.74 rows=25444 width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
Merge Cond: ((retailerlist.retailer_code)::text = ("Order".retailer_code)::text)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25) (actual time=0.018.
.180.502 rows=144211 loops=1)
-> Sort (cost=77651.75..77708.45 rows=22681 width=324) (actual time=772.782..786.401 rows=52378 loops=1)
Sort Key: "Order".retailer_code
Sort Method: quicksort Memory: 15449kB
-> Nested Loop Left Join (cost=20331.25..76010.87 rows=22681 width=324) (actual time=432.162..623.429 rows=52378 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Merge Join (cost=20330.82..21903.80 rows=25657 width=338) (actual time=431.541..519.748 rows=26189 loops=1)
Merge Cond: ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827 width=19) (actual time=0.014..4
1.418 rows=54892 loops=1)
-> Sort (cost=20329.65..20393.79 rows=25657 width=326) (actual time=429.095..434.239 rows=26189 loops=1)
Sort Key: orderdeliverynote.assigned_to_username
Sort Method: quicksort Memory: 7725kB
-> Gather (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950 rows=26189 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671 rows=13094 loops=2)
-> Parallel Index Scan using dbr_code_128932 on torder "Order" (cost=0.43..3219.36 rows=15713 width=25
9) (actual time=0.075..60.880 rows=13094 loops=2)
Index Cond: ((dbr_code)::text = '304717'::text)
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partiall
y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 2134
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.56..0.73 rows=1
width=67) (actual time=0.023..0.023 rows=1 loops=26189)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2 loops=26189)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10) (actual time=0.600..54.705 ro
ws=2 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 81185
Planning Time: 4.242 ms
Execution Time: 1204.463 ms
(39 rows)
Postgresql-13
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.289..1365687.314 rows=5 loops=1)
-> Unique (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.287..1365687.310 rows=5 loops=1)
-> Sort (cost=504.66..504.67 rows=1 width=1592) (actual time=1365687.279..1365687.288 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 11262kB
-> Group (cost=504.60..504.65 rows=1 width=1592) (actual time=1365623.456..1365652.824 rows=36615 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Sort (cost=504.60..504.61 rows=1 width=1588) (actual time=1365623.429..1365629.121 rows=36615 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username
, orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_
user.first_name, "Order".parent_order_no
Sort Method: quicksort Memory: 11259kB
-> Nested Loop (cost=78.24..504.59 rows=1 width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
-> Nested Loop Left Join (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549 rows=36615 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Nested Loop (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064 rows=36615 loops=1)
-> Nested Loop (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436 rows=36615 loops=1)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59 rows=1 width=684) (a
ctual time=0.028..928.347 rows=221068 loops=1)
-> Bitmap Heap Scan on torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual time=6.163..6.164 rows=0 loops=
221068)
Recheck Cond: (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text = (retailerlist.retailer_code)::
text))
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"
}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 0
Heap Blocks: exact=41645
-> BitmapAnd (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0 loops=221068)
-> Bitmap Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual time=5.054..5.054
rows=42741 loops=221068)
Index Cond: ((dbr_code)::text = '304717'::text)
-> Bitmap Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0) (actual time=0.014..
0.014 rows=28 loops=221068)
Index Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.55..307.20 rows=6799 width=424) (a
ctual time=0.014..0.014 rows=1 loops=36615)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..49.41 rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172) (actual time=46.270..46.270 row
s=0 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 144656
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=36
615)
Index Cond: ((username)::text = (orderdeliverynote.assigned_to_username)::text)
Planning Time: 2.019 ms
Execution Time: 1365688.026 ms
(38 rows)
Query:
EXPLAIN (ANALYZE) select DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username, OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date, OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet, DSE_user.first_name, "Order".parent_order_no,
CASE WHEN "Order"."order_status"='dispatched' then 3
WHEN "Order"."order_status"='partially dispatched' then 3
WHEN "Order"."order_status"='acknowledgement pending' then 4
WHEN "Order"."order_status"='delivered' then 2
WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS "Order__actionable"
from torder "Order"
join tretailer_mst as RetailerList on "Order".retailer_code=RetailerList.retailer_code
join torder_delivery_note as OrderDeliveryNote on "Order".order_no=OrderDeliveryNote.order_no
left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and leave.date='2021-06-15' and leave.role='DB'
join tuser_mst DSE_user on OrderDeliveryNote.assigned_to_username=DSE_user.username
where dbr_code='304717' AND (order_status IN (('delivered'),('dn pending'),('acknowledgement pending'),
('partially delivered')) or ((order_status = 'dispatched' or order_status = 'partially dispatched') and leave.date = '2021-06-15'))
group by "Order".order_no ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username, OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date, OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet, DSE_user.first_name, "Order".parent_order_no
order by OrderDeliveryNote.date desc limit 5;
Regards,
Ram Pratap.
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: 16 June 2021 00:28
To: Ram Pratap Maurya; PostgreSQL mailing lists
Subject: Re: BUG #17059: postgresql 13 version problem related to query.
út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <noreply@postgresql.org<mailto:noreply@postgresql.org>> napsal:
The following bug has been logged on the website:
Bug reference: 17059
Logged by: Ram Pratatp maurya
Email address: ram.maurya@lavainternational.in<mailto:ram.maurya@lavainternational.in>
PostgreSQL version: 13.0
Operating system: RHEL 8.3
Description:
Hi team,
I am facing problem related to query.
I have two DB server one is running on postgres12 (RHEL 6) and second is
running on postgresql -13 (RHEL 8.3).
Server H/W configuration is same and postgresql.conf file configuration
parameter is also same.
When I am running below queary on server postgresql-12 data comes within 2
min and when I run this quear on postgresql-13 server its take 30 min
display result.
Can you please suggest any problem in postgresql-13.
Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
postgresql-12 is 350 GB.
.........................................................................................................................................................................................................
select
DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no,
CASE WHEN "Order"."order_status"='dispatched' then 3
WHEN "Order"."order_status"='partially dispatched' then 3
WHEN "Order"."order_status"='acknowledgement pending' then 4
WHEN "Order"."order_status"='delivered' then 2
WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
"Order__actionable"
from torder "Order"
join tretailer_mst as RetailerList on
"Order".retailer_code=RetailerList.retailer_code
join torder_delivery_note as OrderDeliveryNote on
"Order".order_no=OrderDeliveryNote.order_no
left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
leave.date='2021-06-15' and leave.role='DB'
join tuser_mst DSE_user on
OrderDeliveryNote.assigned_to_username=DSE_user.username
where dbr_code='304717' AND (order_status IN (('delivered'),('dn
pending'),('acknowledgement pending'),
('partially delivered')) or ((order_status = 'dispatched' or order_status =
'partially dispatched') and leave.date = '2021-06-15'))
group by "Order".order_no
,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no
order by OrderDeliveryNote.date desc limit 50;
please, send result of explain analyze for pg 12 and pg 13
https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://explain.depesz.com/
Regards
Pavel
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 19:42 Pavel Stehule <pavel.stehule@gmail.com>
parent: Ram Pratap Maurya <ram.maurya@lavainternational.in>
0 siblings, 2 replies; 10+ messages in thread
From: Pavel Stehule @ 2021-06-15 19:42 UTC (permalink / raw)
To: Ram Pratap Maurya <ram.maurya@lavainternational.in>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
út 15. 6. 2021 v 21:36 odesílatel Ram Pratap Maurya <
ram.maurya@lavainternational.in> napsal:
> Dear Team,
>
>
>
> Please find EXPLAIN ANALYZE details of Query:
>
>
>
>
>
> Postgresql-12 :
>
>
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=87069.03..87069.21 rows=5 width=346) (actual
> time=1202.073..1202.090 rows=5 loops=1)
>
> -> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual
> time=1202.071..1202.086 rows=5 loops=1)
>
> -> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual
> time=1202.070..1202.070 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 7922kB
>
> -> HashAggregate (cost=84634.67..85207.16 rows=25444
> width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Merge Join (cost=77821.29..83807.74 rows=25444
> width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
>
> Merge Cond: ((retailerlist.retailer_code)::text
> = ("Order".retailer_code)::text)
>
> -> Index Scan using retailer_code_12390127_idx
> on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25)
> (actual time=0.018.
>
> .180.502 rows=144211 loops=1)
>
> -> Sort (cost=77651.75..77708.45 rows=22681
> width=324) (actual time=772.782..786.401 rows=52378 loops=1)
>
> Sort Key: "Order".retailer_code
>
> Sort Method: quicksort Memory: 15449kB
>
> -> Nested Loop Left Join
> (cost=20331.25..76010.87 rows=22681 width=324) (actual
> time=432.162..623.429 rows=52378 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Merge Join
> (cost=20330.82..21903.80 rows=25657 width=338) (actual
> time=431.541..519.748 rows=26189 loops=1)
>
> Merge Cond:
> ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
>
> -> Index Scan using
> username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827
> width=19) (actual time=0.014..4
>
> 1.418 rows=54892 loops=1)
>
> -> Sort
> (cost=20329.65..20393.79 rows=25657 width=326) (actual
> time=429.095..434.239 rows=26189 loops=1)
>
> Sort Key:
> orderdeliverynote.assigned_to_username
>
> Sort Method: quicksort
> Memory: 7725kB
>
> -> Gather
> (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950
> rows=26189 loops=1)
>
> Workers Planned: 1
>
> Workers Launched:
> 1
>
> -> Nested Loop
> (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671
> rows=13094 loops=2)
>
> ->
> Parallel Index Scan using dbr_code_128932 on torder "Order"
> (cost=0.43..3219.36 rows=15713 width=25
>
> 9) (actual time=0.075..60.880 rows=13094 loops=2)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
>
> Filter: (((order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partiall
>
> y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows
> Removed by Filter: 2134
>
> -> Index
> Scan using order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.56..0.73 rows=1
>
> width=67) (actual time=0.023..0.023 rows=1 loops=26189)
>
> Index
> Cond: ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize
> (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2
> loops=26189)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10)
> (actual time=0.600..54.705 ro
>
> ws=2 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 81185
>
> Planning Time: 4.242 ms
>
> Execution Time: 1204.463 ms
>
> (39 rows)
>
>
>
>
>
>
> Postgresql-13
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.289..1365687.314 rows=5 loops=1)
>
> -> Unique (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.287..1365687.310 rows=5 loops=1)
>
> -> Sort (cost=504.66..504.67 rows=1 width=1592) (actual
> time=1365687.279..1365687.288 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 11262kB
>
> -> Group (cost=504.60..504.65 rows=1 width=1592) (actual
> time=1365623.456..1365652.824 rows=36615 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Sort (cost=504.60..504.61 rows=1 width=1588)
> (actual time=1365623.429..1365629.121 rows=36615 loops=1)
>
> Sort Key: orderdeliverynote.date DESC,
> "Order".order_no, "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username
>
> , orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_
>
> user.first_name, "Order".parent_order_no
>
> Sort Method: quicksort Memory: 11259kB
>
> -> Nested Loop (cost=78.24..504.59 rows=1
> width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
>
> -> Nested Loop Left Join
> (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549
> rows=36615 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Nested Loop
> (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064
> rows=36615 loops=1)
>
> -> Nested Loop
> (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436
> rows=36615 loops=1)
>
> -> Index Scan using
> retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59
> rows=1 width=684) (a
>
> ctual time=0.028..928.347 rows=221068 loops=1)
>
> -> Bitmap Heap Scan on
> torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual
> time=6.163..6.164 rows=0 loops=
>
> 221068)
>
> Recheck Cond:
> (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text =
> (retailerlist.retailer_code)::
>
> text))
>
> Filter:
> (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement
> pending","partially delivered"
>
> }'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows Removed by
> Filter: 0
>
> Heap Blocks:
> exact=41645
>
> -> BitmapAnd
> (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0
> loops=221068)
>
> -> Bitmap
> Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual
> time=5.054..5.054
>
> rows=42741 loops=221068)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
> -> Bitmap
> Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0)
> (actual time=0.014..
>
> 0.014 rows=28 loops=221068)
>
> Index
> Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
>
> -> Index Scan using
> order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.55..307.20 rows=6799 width=424) (a
>
> ctual time=0.014..0.014 rows=1 loops=36615)
>
> Index Cond:
> ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize (cost=0.42..49.41
> rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172)
> (actual time=46.270..46.270 row
>
> s=0 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 144656
>
> -> Index Scan using username_12891 on
> tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual
> time=0.005..0.005 rows=1 loops=36
>
> 615)
>
> Index Cond: ((username)::text =
> (orderdeliverynote.assigned_to_username)::text)
>
> Planning Time: 2.019 ms
>
> Execution Time: 1365688.026 ms
>
> (38 rows)
>
>
>
>
>
There are bad estimations - did you run ANALYZE?
Pavel
>
>
>
> *Query:*
>
>
>
>
>
> EXPLAIN (ANALYZE) select
> DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
>
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
>
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no,
>
> CASE WHEN "Order"."order_status"='dispatched' then 3
>
> WHEN "Order"."order_status"='partially dispatched' then 3
>
> WHEN "Order"."order_status"='acknowledgement pending' then 4
>
> WHEN "Order"."order_status"='delivered' then 2
>
> WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
> "Order__actionable"
>
> from torder "Order"
>
> join tretailer_mst as RetailerList on
> "Order".retailer_code=RetailerList.retailer_code
>
> join torder_delivery_note as OrderDeliveryNote on
> "Order".order_no=OrderDeliveryNote.order_no
>
> left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
> leave.date='2021-06-15' and leave.role='DB'
>
> join tuser_mst DSE_user on
> OrderDeliveryNote.assigned_to_username=DSE_user.username
>
> where dbr_code='304717' AND (order_status IN (('delivered'),('dn
> pending'),('acknowledgement pending'),
>
> ('partially delivered')) or ((order_status = 'dispatched' or order_status
> = 'partially dispatched') and leave.date = '2021-06-15'))
>
> group by "Order".order_no
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
>
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
>
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
>
> order by OrderDeliveryNote.date desc limit 5;
>
>
>
>
>
>
>
> Regards,
>
> Ram Pratap.
>
>
>
> *From:* Pavel Stehule [mailto:pavel.stehule@gmail.com]
> *Sent:* 16 June 2021 00:28
> *To:* Ram Pratap Maurya; PostgreSQL mailing lists
> *Subject:* Re: BUG #17059: postgresql 13 version problem related to query.
>
>
>
>
>
>
>
> út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <
> noreply@postgresql.org> napsal:
>
> The following bug has been logged on the website:
>
> Bug reference: 17059
> Logged by: Ram Pratatp maurya
> Email address: ram.maurya@lavainternational.in
> PostgreSQL version: 13.0
> Operating system: RHEL 8.3
> Description:
>
> Hi team,
>
> I am facing problem related to query.
>
> I have two DB server one is running on postgres12 (RHEL 6) and second is
> running on postgresql -13 (RHEL 8.3).
> Server H/W configuration is same and postgresql.conf file configuration
> parameter is also same.
>
> When I am running below queary on server postgresql-12 data comes within 2
> min and when I run this quear on postgresql-13 server its take 30 min
> display result.
>
> Can you please suggest any problem in postgresql-13.
>
> Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
> postgresql-12 is 350 GB.
>
>
> .........................................................................................................................................................................................................
> select
>
> DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no,
> CASE WHEN "Order"."order_status"='dispatched' then 3
> WHEN "Order"."order_status"='partially dispatched' then 3
> WHEN "Order"."order_status"='acknowledgement pending' then 4
> WHEN "Order"."order_status"='delivered' then 2
> WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
> "Order__actionable"
> from torder "Order"
> join tretailer_mst as RetailerList on
> "Order".retailer_code=RetailerList.retailer_code
> join torder_delivery_note as OrderDeliveryNote on
> "Order".order_no=OrderDeliveryNote.order_no
> left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
> leave.date='2021-06-15' and leave.role='DB'
> join tuser_mst DSE_user on
> OrderDeliveryNote.assigned_to_username=DSE_user.username
> where dbr_code='304717' AND (order_status IN (('delivered'),('dn
> pending'),('acknowledgement pending'),
> ('partially delivered')) or ((order_status = 'dispatched' or order_status =
> 'partially dispatched') and leave.date = '2021-06-15'))
> group by "Order".order_no
>
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
> order by OrderDeliveryNote.date desc limit 50;
>
>
>
> please, send result of explain analyze for pg 12 and pg 13
>
>
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> https://explain.depesz.com/
>
>
>
> Regards
>
>
>
> Pavel
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 19:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2021-06-15 19:54 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Ram Pratap Maurya <ram.maurya@lavainternational.in>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Pavel Stehule <pavel.stehule@gmail.com> writes:
> út 15. 6. 2021 v 21:36 odesílatel Ram Pratap Maurya <
> ram.maurya@lavainternational.in> napsal:
>> Postgresql-12 :
>> -> Index Scan using retailer_code_12390127_idx
>> on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25)
>> (actual time=0.018..180.502 rows=144211 loops=1)
>>
>> Postgresql-13
>> -> Index Scan using
>> retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59
>> rows=1 width=684) (actual time=0.028..928.347 rows=221068 loops=1)
There seem to be rather serious discrepancies between rowcount estimates
and reality on the v13 installation. Maybe an ANALYZE would help.
I'm also wondering why auto-analyze hasn't come along and improved
the situation.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 20:00 Ram Pratap Maurya <ram.maurya@lavainternational.in>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ram Pratap Maurya @ 2021-06-15 20:00 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Dear Pavel,
I have run “EXPLAIN ANALYZE” and below is result of Postgresql-12 and Postgresql-13 result , screenshot also attached for your reference.
Postgresql-12 :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=87069.03..87069.21 rows=5 width=346) (actual time=1202.073..1202.090 rows=5 loops=1)
-> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual time=1202.071..1202.086 rows=5 loops=1)
-> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual time=1202.070..1202.070 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 7922kB
-> HashAggregate (cost=84634.67..85207.16 rows=25444 width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Merge Join (cost=77821.29..83807.74 rows=25444 width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
Merge Cond: ((retailerlist.retailer_code)::text = ("Order".retailer_code)::text)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25) (actual time=0.018.
.180.502 rows=144211 loops=1)
-> Sort (cost=77651.75..77708.45 rows=22681 width=324) (actual time=772.782..786.401 rows=52378 loops=1)
Sort Key: "Order".retailer_code
Sort Method: quicksort Memory: 15449kB
-> Nested Loop Left Join (cost=20331.25..76010.87 rows=22681 width=324) (actual time=432.162..623.429 rows=52378 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Merge Join (cost=20330.82..21903.80 rows=25657 width=338) (actual time=431.541..519.748 rows=26189 loops=1)
Merge Cond: ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827 width=19) (actual time=0.014..4
1.418 rows=54892 loops=1)
-> Sort (cost=20329.65..20393.79 rows=25657 width=326) (actual time=429.095..434.239 rows=26189 loops=1)
Sort Key: orderdeliverynote.assigned_to_username
Sort Method: quicksort Memory: 7725kB
-> Gather (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950 rows=26189 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671 rows=13094 loops=2)
-> Parallel Index Scan using dbr_code_128932 on torder "Order" (cost=0.43..3219.36 rows=15713 width=25
9) (actual time=0.075..60.880 rows=13094 loops=2)
Index Cond: ((dbr_code)::text = '304717'::text)
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partiall
y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 2134
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.56..0.73 rows=1
width=67) (actual time=0.023..0.023 rows=1 loops=26189)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2 loops=26189)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10) (actual time=0.600..54.705 ro
ws=2 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 81185
Planning Time: 4.242 ms
Execution Time: 1204.463 ms
(39 rows)
Postgresql-13
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.289..1365687.314 rows=5 loops=1)
-> Unique (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.287..1365687.310 rows=5 loops=1)
-> Sort (cost=504.66..504.67 rows=1 width=1592) (actual time=1365687.279..1365687.288 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 11262kB
-> Group (cost=504.60..504.65 rows=1 width=1592) (actual time=1365623.456..1365652.824 rows=36615 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Sort (cost=504.60..504.61 rows=1 width=1588) (actual time=1365623.429..1365629.121 rows=36615 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username
, orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_
user.first_name, "Order".parent_order_no
Sort Method: quicksort Memory: 11259kB
-> Nested Loop (cost=78.24..504.59 rows=1 width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
-> Nested Loop Left Join (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549 rows=36615 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Nested Loop (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064 rows=36615 loops=1)
-> Nested Loop (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436 rows=36615 loops=1)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59 rows=1 width=684) (a
ctual time=0.028..928.347 rows=221068 loops=1)
-> Bitmap Heap Scan on torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual time=6.163..6.164 rows=0 loops=
221068)
Recheck Cond: (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text = (retailerlist.retailer_code)::
text))
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"
}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 0
Heap Blocks: exact=41645
-> BitmapAnd (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0 loops=221068)
-> Bitmap Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual time=5.054..5.054
rows=42741 loops=221068)
Index Cond: ((dbr_code)::text = '304717'::text)
-> Bitmap Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0) (actual time=0.014..
0.014 rows=28 loops=221068)
Index Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.55..307.20 rows=6799 width=424) (a
ctual time=0.014..0.014 rows=1 loops=36615)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..49.41 rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172) (actual time=46.270..46.270 row
s=0 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 144656
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=36
615)
Index Cond: ((username)::text = (orderdeliverynote.assigned_to_username)::text)
Planning Time: 2.019 ms
Execution Time: 1365688.026 ms
(38 rows)
Regards,
Ram Pratap.
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: 16 June 2021 01:12
To: Ram Pratap Maurya
Cc: PostgreSQL mailing lists
Subject: Re: BUG #17059: postgresql 13 version problem related to query.
út 15. 6. 2021 v 21:36 odesílatel Ram Pratap Maurya <ram.maurya@lavainternational.in<mailto:ram.maurya@lavainternational.in>> napsal:
Dear Team,
Please find EXPLAIN ANALYZE details of Query:
Postgresql-12 :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=87069.03..87069.21 rows=5 width=346) (actual time=1202.073..1202.090 rows=5 loops=1)
-> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual time=1202.071..1202.086 rows=5 loops=1)
-> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual time=1202.070..1202.070 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 7922kB
-> HashAggregate (cost=84634.67..85207.16 rows=25444 width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Merge Join (cost=77821.29..83807.74 rows=25444 width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
Merge Cond: ((retailerlist.retailer_code)::text = ("Order".retailer_code)::text)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25) (actual time=0.018.
.180.502 rows=144211 loops=1)
-> Sort (cost=77651.75..77708.45 rows=22681 width=324) (actual time=772.782..786.401 rows=52378 loops=1)
Sort Key: "Order".retailer_code
Sort Method: quicksort Memory: 15449kB
-> Nested Loop Left Join (cost=20331.25..76010.87 rows=22681 width=324) (actual time=432.162..623.429 rows=52378 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Merge Join (cost=20330.82..21903.80 rows=25657 width=338) (actual time=431.541..519.748 rows=26189 loops=1)
Merge Cond: ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827 width=19) (actual time=0.014..4
1.418 rows=54892 loops=1)
-> Sort (cost=20329.65..20393.79 rows=25657 width=326) (actual time=429.095..434.239 rows=26189 loops=1)
Sort Key: orderdeliverynote.assigned_to_username
Sort Method: quicksort Memory: 7725kB
-> Gather (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950 rows=26189 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671 rows=13094 loops=2)
-> Parallel Index Scan using dbr_code_128932 on torder "Order" (cost=0.43..3219.36 rows=15713 width=25
9) (actual time=0.075..60.880 rows=13094 loops=2)
Index Cond: ((dbr_code)::text = '304717'::text)
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partiall
y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 2134
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.56..0.73 rows=1
width=67) (actual time=0.023..0.023 rows=1 loops=26189)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2 loops=26189)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10) (actual time=0.600..54.705 ro
ws=2 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 81185
Planning Time: 4.242 ms
Execution Time: 1204.463 ms
(39 rows)
Postgresql-13
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Limit (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.289..1365687.314 rows=5 loops=1)
-> Unique (cost=504.66..504.70 rows=1 width=1592) (actual time=1365687.287..1365687.310 rows=5 loops=1)
-> Sort (cost=504.66..504.67 rows=1 width=1592) (actual time=1365687.279..1365687.288 rows=5 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdeliv
erynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first_n
ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text = 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially dispatched'::text)
THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement pending'::text) THEN 4 WHEN (("Order".order_status)::text = 'delivered'::text) THEN 2 WHEN (("Order".order
_status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
Sort Method: quicksort Memory: 11262kB
-> Group (cost=504.60..504.65 rows=1 width=1592) (actual time=1365623.456..1365652.824 rows=36615 loops=1)
Group Key: orderdeliverynote.date, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username, orderdel
iverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_user.first
_name, "Order".parent_order_no
-> Sort (cost=504.60..504.61 rows=1 width=1588) (actual time=1365623.429..1365629.121 rows=36615 loops=1)
Sort Key: orderdeliverynote.date DESC, "Order".order_no, "Order".retailer_code, "Order".order_status, orderdeliverynote.assigned_to_username
, orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no, orderdeliverynote.total_value, leave.date, leave.username, retailerlist.retailer_outlet, dse_
user.first_name, "Order".parent_order_no
Sort Method: quicksort Memory: 11259kB
-> Nested Loop (cost=78.24..504.59 rows=1 width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
-> Nested Loop Left Join (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549 rows=36615 loops=1)
Join Filter: (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
Filter: ((("Order".order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"}'::text[]
)) OR (((("Order".order_status)::text = 'dispatched'::text) OR (("Order".order_status)::text = 'partially dispatched'::text)) AND (leave.date = '2021-06-15'::date)))
-> Nested Loop (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064 rows=36615 loops=1)
-> Nested Loop (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436 rows=36615 loops=1)
-> Index Scan using retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59 rows=1 width=684) (a
ctual time=0.028..928.347 rows=221068 loops=1)
-> Bitmap Heap Scan on torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual time=6.163..6.164 rows=0 loops=
221068)
Recheck Cond: (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text = (retailerlist.retailer_code)::
text))
Filter: (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement pending","partially delivered"
}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR ((order_status)::text = 'partially dispatched'::text))
Rows Removed by Filter: 0
Heap Blocks: exact=41645
-> BitmapAnd (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0 loops=221068)
-> Bitmap Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual time=5.054..5.054
rows=42741 loops=221068)
Index Cond: ((dbr_code)::text = '304717'::text)
-> Bitmap Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0) (actual time=0.014..
0.014 rows=28 loops=221068)
Index Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
-> Index Scan using order_no_128903 on torder_delivery_note orderdeliverynote (cost=0.55..307.20 rows=6799 width=424) (a
ctual time=0.014..0.014 rows=1 loops=36615)
Index Cond: ((order_no)::text = ("Order".order_no)::text)
-> Materialize (cost=0.42..49.41 rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
-> Index Scan using role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172) (actual time=46.270..46.270 row
s=0 loops=1)
Index Cond: ((role)::text = 'DB'::text)
Filter: (date = '2021-06-15'::date)
Rows Removed by Filter: 144656
-> Index Scan using username_12891 on tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=36
615)
Index Cond: ((username)::text = (orderdeliverynote.assigned_to_username)::text)
Planning Time: 2.019 ms
Execution Time: 1365688.026 ms
(38 rows)
There are bad estimations - did you run ANALYZE?
Pavel
Query:
EXPLAIN (ANALYZE) select DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username, OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date, OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet, DSE_user.first_name, "Order".parent_order_no,
CASE WHEN "Order"."order_status"='dispatched' then 3
WHEN "Order"."order_status"='partially dispatched' then 3
WHEN "Order"."order_status"='acknowledgement pending' then 4
WHEN "Order"."order_status"='delivered' then 2
WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS "Order__actionable"
from torder "Order"
join tretailer_mst as RetailerList on "Order".retailer_code=RetailerList.retailer_code
join torder_delivery_note as OrderDeliveryNote on "Order".order_no=OrderDeliveryNote.order_no
left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and leave.date='2021-06-15' and leave.role='DB'
join tuser_mst DSE_user on OrderDeliveryNote.assigned_to_username=DSE_user.username
where dbr_code='304717' AND (order_status IN (('delivered'),('dn pending'),('acknowledgement pending'),
('partially delivered')) or ((order_status = 'dispatched' or order_status = 'partially dispatched') and leave.date = '2021-06-15'))
group by "Order".order_no ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username, OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date, OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet, DSE_user.first_name, "Order".parent_order_no
order by OrderDeliveryNote.date desc limit 5;
Regards,
Ram Pratap.
From: Pavel Stehule [mailto:pavel.stehule@gmail.com<mailto:pavel.stehule@gmail.com>]
Sent: 16 June 2021 00:28
To: Ram Pratap Maurya; PostgreSQL mailing lists
Subject: Re: BUG #17059: postgresql 13 version problem related to query.
út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <noreply@postgresql.org<mailto:noreply@postgresql.org>> napsal:
The following bug has been logged on the website:
Bug reference: 17059
Logged by: Ram Pratatp maurya
Email address: ram.maurya@lavainternational.in<mailto:ram.maurya@lavainternational.in>
PostgreSQL version: 13.0
Operating system: RHEL 8.3
Description:
Hi team,
I am facing problem related to query.
I have two DB server one is running on postgres12 (RHEL 6) and second is
running on postgresql -13 (RHEL 8.3).
Server H/W configuration is same and postgresql.conf file configuration
parameter is also same.
When I am running below queary on server postgresql-12 data comes within 2
min and when I run this quear on postgresql-13 server its take 30 min
display result.
Can you please suggest any problem in postgresql-13.
Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
postgresql-12 is 350 GB.
.........................................................................................................................................................................................................
select
DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no,
CASE WHEN "Order"."order_status"='dispatched' then 3
WHEN "Order"."order_status"='partially dispatched' then 3
WHEN "Order"."order_status"='acknowledgement pending' then 4
WHEN "Order"."order_status"='delivered' then 2
WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
"Order__actionable"
from torder "Order"
join tretailer_mst as RetailerList on
"Order".retailer_code=RetailerList.retailer_code
join torder_delivery_note as OrderDeliveryNote on
"Order".order_no=OrderDeliveryNote.order_no
left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
leave.date='2021-06-15' and leave.role='DB'
join tuser_mst DSE_user on
OrderDeliveryNote.assigned_to_username=DSE_user.username
where dbr_code='304717' AND (order_status IN (('delivered'),('dn
pending'),('acknowledgement pending'),
('partially delivered')) or ((order_status = 'dispatched' or order_status =
'partially dispatched') and leave.date = '2021-06-15'))
group by "Order".order_no
,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
OrderDeliveryNote.assigned_to_role,
OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
OrderDeliveryNote.total_value,
leave.date, leave.username, RetailerList.retailer_outlet,
DSE_user.first_name, "Order".parent_order_no
order by OrderDeliveryNote.date desc limit 50;
please, send result of explain analyze for pg 12 and pg 13
https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://explain.depesz.com/
Regards
Pavel
Attachments:
PG13-2.JPG (image/jpeg, 213.0K) download
view image
PG12-2.JPG (image/jpeg, 207.8K) download
view image
PG13-1.JPG (image/jpeg, 246.4K) download
view image
PG12-1.JPG (image/jpeg, 230.2K) download
view image
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 20:02 Pavel Stehule <pavel.stehule@gmail.com>
parent: Ram Pratap Maurya <ram.maurya@lavainternational.in>
0 siblings, 1 reply; 10+ messages in thread
From: Pavel Stehule @ 2021-06-15 20:02 UTC (permalink / raw)
To: Ram Pratap Maurya <ram.maurya@lavainternational.in>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
út 15. 6. 2021 v 22:00 odesílatel Ram Pratap Maurya <
ram.maurya@lavainternational.in> napsal:
> Dear Pavel,
>
>
>
> I have run “EXPLAIN ANALYZE” and below is result of Postgresql-12 and
> Postgresql-13 result , screenshot also attached for your reference.
>
>
>
>
>
>
>
> Postgresql-12 :
>
>
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=87069.03..87069.21 rows=5 width=346) (actual
> time=1202.073..1202.090 rows=5 loops=1)
>
> -> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual
> time=1202.071..1202.086 rows=5 loops=1)
>
> -> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual
> time=1202.070..1202.070 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 7922kB
>
> -> HashAggregate (cost=84634.67..85207.16 rows=25444
> width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Merge Join (cost=77821.29..83807.74 rows=25444
> width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
>
> Merge Cond: ((retailerlist.retailer_code)::text
> = ("Order".retailer_code)::text)
>
> -> Index Scan using retailer_code_12390127_idx
> on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25)
> (actual time=0.018.
>
> .180.502 rows=144211 loops=1)
>
> -> Sort (cost=77651.75..77708.45 rows=22681
> width=324) (actual time=772.782..786.401 rows=52378 loops=1)
>
> Sort Key: "Order".retailer_code
>
> Sort Method: quicksort Memory: 15449kB
>
> -> Nested Loop Left Join
> (cost=20331.25..76010.87 rows=22681 width=324) (actual
> time=432.162..623.429 rows=52378 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Merge Join
> (cost=20330.82..21903.80 rows=25657 width=338) (actual
> time=431.541..519.748 rows=26189 loops=1)
>
> Merge Cond:
> ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
>
> -> Index Scan using
> username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827
> width=19) (actual time=0.014..4
>
> 1.418 rows=54892 loops=1)
>
> -> Sort
> (cost=20329.65..20393.79 rows=25657 width=326) (actual
> time=429.095..434.239 rows=26189 loops=1)
>
> Sort Key:
> orderdeliverynote.assigned_to_username
>
> Sort Method: quicksort
> Memory: 7725kB
>
> -> Gather
> (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950
> rows=26189 loops=1)
>
> Workers Planned: 1
>
> Workers Launched:
> 1
>
> -> Nested Loop
> (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671
> rows=13094 loops=2)
>
> ->
> Parallel Index Scan using dbr_code_128932 on torder "Order"
> (cost=0.43..3219.36 rows=15713 width=25
>
> 9) (actual time=0.075..60.880 rows=13094 loops=2)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
>
> Filter: (((order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partiall
>
> y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows
> Removed by Filter: 2134
>
> -> Index
> Scan using order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.56..0.73 rows=1
>
> width=67) (actual time=0.023..0.023 rows=1 loops=26189)
>
> Index
> Cond: ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize
> (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2
> loops=26189)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10)
> (actual time=0.600..54.705 ro
>
> ws=2 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 81185
>
> Planning Time: 4.242 ms
>
> Execution Time: 1204.463 ms
>
> (39 rows)
>
>
>
>
>
>
> Postgresql-13
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.289..1365687.314 rows=5 loops=1)
>
> -> Unique (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.287..1365687.310 rows=5 loops=1)
>
> -> Sort (cost=504.66..504.67 rows=1 width=1592) (actual
> time=1365687.279..1365687.288 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 11262kB
>
> -> Group (cost=504.60..504.65 rows=1 width=1592) (actual
> time=1365623.456..1365652.824 rows=36615 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Sort (cost=504.60..504.61 rows=1 width=1588)
> (actual time=1365623.429..1365629.121 rows=36615 loops=1)
>
> Sort Key: orderdeliverynote.date DESC,
> "Order".order_no, "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username
>
> , orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_
>
> user.first_name, "Order".parent_order_no
>
> Sort Method: quicksort Memory: 11259kB
>
> -> Nested Loop (cost=78.24..504.59 rows=1
> width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
>
> -> Nested Loop Left Join
> (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549
> rows=36615 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Nested Loop
> (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064
> rows=36615 loops=1)
>
> -> Nested Loop
> (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436
> rows=36615 loops=1)
>
> -> Index Scan using
> retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59
> rows=1 width=684) (a
>
> ctual time=0.028..928.347 rows=221068 loops=1)
>
> -> Bitmap Heap Scan on
> torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual
> time=6.163..6.164 rows=0 loops=
>
> 221068)
>
> Recheck Cond:
> (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text =
> (retailerlist.retailer_code)::
>
> text))
>
> Filter:
> (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement
> pending","partially delivered"
>
> }'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows Removed by
> Filter: 0
>
> Heap Blocks:
> exact=41645
>
> -> BitmapAnd
> (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0
> loops=221068)
>
> -> Bitmap
> Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual
> time=5.054..5.054
>
> rows=42741 loops=221068)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
> -> Bitmap
> Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0)
> (actual time=0.014..
>
> 0.014 rows=28 loops=221068)
>
> Index
> Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
>
> -> Index Scan using
> order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.55..307.20 rows=6799 width=424) (a
>
> ctual time=0.014..0.014 rows=1 loops=36615)
>
> Index Cond:
> ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize (cost=0.42..49.41
> rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172)
> (actual time=46.270..46.270 row
>
> s=0 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 144656
>
> -> Index Scan using username_12891 on
> tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual
> time=0.005..0.005 rows=1 loops=36
>
> 615)
>
> Index Cond: ((username)::text =
> (orderdeliverynote.assigned_to_username)::text)
>
> Planning Time: 2.019 ms
>
> Execution Time: 1365688.026 ms
>
> (38 rows)
>
>
>
>
>
> *Regards,*
>
> *Ram Pratap.*
>
>
>
>
>
>
>
> *From:* Pavel Stehule [mailto:pavel.stehule@gmail.com]
> *Sent:* 16 June 2021 01:12
> *To:* Ram Pratap Maurya
> *Cc:* PostgreSQL mailing lists
> *Subject:* Re: BUG #17059: postgresql 13 version problem related to query.
>
>
>
>
>
>
>
> út 15. 6. 2021 v 21:36 odesílatel Ram Pratap Maurya <
> ram.maurya@lavainternational.in> napsal:
>
> Dear Team,
>
>
>
> Please find EXPLAIN ANALYZE details of Query:
>
>
>
>
>
> Postgresql-12 :
>
>
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=87069.03..87069.21 rows=5 width=346) (actual
> time=1202.073..1202.090 rows=5 loops=1)
>
> -> Unique (cost=87069.03..88023.18 rows=25444 width=346) (actual
> time=1202.071..1202.086 rows=5 loops=1)
>
> -> Sort (cost=87069.03..87132.64 rows=25444 width=346) (actual
> time=1202.070..1202.070 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 7922kB
>
> -> HashAggregate (cost=84634.67..85207.16 rows=25444
> width=346) (actual time=1077.387..1097.135 rows=26930 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Merge Join (cost=77821.29..83807.74 rows=25444
> width=342) (actual time=779.854..1043.354 rows=26930 loops=1)
>
> Merge Cond: ((retailerlist.retailer_code)::text
> = ("Order".retailer_code)::text)
>
> -> Index Scan using retailer_code_12390127_idx
> on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923 width=25)
> (actual time=0.018.
>
> .180.502 rows=144211 loops=1)
>
> -> Sort (cost=77651.75..77708.45 rows=22681
> width=324) (actual time=772.782..786.401 rows=52378 loops=1)
>
> Sort Key: "Order".retailer_code
>
> Sort Method: quicksort Memory: 15449kB
>
> -> Nested Loop Left Join
> (cost=20331.25..76010.87 rows=22681 width=324) (actual
> time=432.162..623.429 rows=52378 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Merge Join
> (cost=20330.82..21903.80 rows=25657 width=338) (actual
> time=431.541..519.748 rows=26189 loops=1)
>
> Merge Cond:
> ((dse_user.username)::text = (orderdeliverynote.assigned_to_username)::text)
>
> -> Index Scan using
> username_12891 on tuser_mst dse_user (cost=0.42..6175.73 rows=320827
> width=19) (actual time=0.014..4
>
> 1.418 rows=54892 loops=1)
>
> -> Sort
> (cost=20329.65..20393.79 rows=25657 width=326) (actual
> time=429.095..434.239 rows=26189 loops=1)
>
> Sort Key:
> orderdeliverynote.assigned_to_username
>
> Sort Method: quicksort
> Memory: 7725kB
>
> -> Gather
> (cost=1000.99..18450.65 rows=25657 width=326) (actual time=1.165..394.950
> rows=26189 loops=1)
>
> Workers Planned: 1
>
> Workers Launched:
> 1
>
> -> Nested Loop
> (cost=0.99..14884.95 rows=15092 width=326) (actual time=0.162..382.671
> rows=13094 loops=2)
>
> ->
> Parallel Index Scan using dbr_code_128932 on torder "Order"
> (cost=0.43..3219.36 rows=15713 width=25
>
> 9) (actual time=0.075..60.880 rows=13094 loops=2)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
>
> Filter: (((order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partiall
>
> y delivered"}'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows
> Removed by Filter: 2134
>
> -> Index
> Scan using order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.56..0.73 rows=1
>
> width=67) (actual time=0.023..0.023 rows=1 loops=26189)
>
> Index
> Cond: ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize
> (cost=0.42..2600.82 rows=73 width=10) (actual time=0.000..0.002 rows=2
> loops=26189)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..2600.45 rows=73 width=10)
> (actual time=0.600..54.705 ro
>
> ws=2 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 81185
>
> Planning Time: 4.242 ms
>
> Execution Time: 1204.463 ms
>
> (39 rows)
>
>
>
>
>
>
> Postgresql-13
>
>
>
> QUERY PLAN
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------
>
> Limit (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.289..1365687.314 rows=5 loops=1)
>
> -> Unique (cost=504.66..504.70 rows=1 width=1592) (actual
> time=1365687.287..1365687.310 rows=5 loops=1)
>
> -> Sort (cost=504.66..504.67 rows=1 width=1592) (actual
> time=1365687.279..1365687.288 rows=5 loops=1)
>
> Sort Key: orderdeliverynote.date DESC, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdeliv
>
> erynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first_n
>
> ame, "Order".parent_order_no, (CASE WHEN (("Order".order_status)::text =
> 'dispatched'::text) THEN 3 WHEN (("Order".order_status)::text = 'partially
> dispatched'::text)
>
> THEN 3 WHEN (("Order".order_status)::text = 'acknowledgement
> pending'::text) THEN 4 WHEN (("Order".order_status)::text =
> 'delivered'::text) THEN 2 WHEN (("Order".order
>
> _status)::text = 'dn pending'::text) THEN 1 ELSE 0 END)
>
> Sort Method: quicksort Memory: 11262kB
>
> -> Group (cost=504.60..504.65 rows=1 width=1592) (actual
> time=1365623.456..1365652.824 rows=36615 loops=1)
>
> Group Key: orderdeliverynote.date, "Order".order_no,
> "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username, orderdel
>
> iverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_user.first
>
> _name, "Order".parent_order_no
>
> -> Sort (cost=504.60..504.61 rows=1 width=1588)
> (actual time=1365623.429..1365629.121 rows=36615 loops=1)
>
> Sort Key: orderdeliverynote.date DESC,
> "Order".order_no, "Order".retailer_code, "Order".order_status,
> orderdeliverynote.assigned_to_username
>
> , orderdeliverynote.assigned_to_role, orderdeliverynote.delivery_note_no,
> orderdeliverynote.total_value, leave.date, leave.username,
> retailerlist.retailer_outlet, dse_
>
> user.first_name, "Order".parent_order_no
>
> Sort Method: quicksort Memory: 11259kB
>
> -> Nested Loop (cost=78.24..504.59 rows=1
> width=1588) (actual time=5822.819..1365512.599 rows=36615 loops=1)
>
> -> Nested Loop Left Join
> (cost=77.81..504.14 rows=1 width=1576) (actual time=5822.790..1365306.549
> rows=36615 loops=1)
>
> Join Filter:
> (("Order".order_no)::text = (orderdeliverynote.order_no)::text)
>
> Filter:
> ((("Order".order_status)::text = ANY ('{delivered,"dn
> pending","acknowledgement pending","partially delivered"}'::text[]
>
> )) OR (((("Order".order_status)::text = 'dispatched'::text) OR
> (("Order".order_status)::text = 'partially dispatched'::text)) AND
> (leave.date = '2021-06-15'::date)))
>
> -> Nested Loop
> (cost=77.40..454.63 rows=2 width=1622) (actual time=5776.507..1365239.064
> rows=36615 loops=1)
>
> -> Nested Loop
> (cost=76.84..79.44 rows=1 width=1198) (actual time=5776.467..1364698.436
> rows=36615 loops=1)
>
> -> Index Scan using
> retailer_code_12390127_idx on tretailer_mst retailerlist (cost=0.38..0.59
> rows=1 width=684) (a
>
> ctual time=0.028..928.347 rows=221068 loops=1)
>
> -> Bitmap Heap Scan on
> torder "Order" (cost=76.47..78.84 rows=1 width=682) (actual
> time=6.163..6.164 rows=0 loops=
>
> 221068)
>
> Recheck Cond:
> (((dbr_code)::text = '304717'::text) AND ((retailer_code)::text =
> (retailerlist.retailer_code)::
>
> text))
>
> Filter:
> (((order_status)::text = ANY ('{delivered,"dn pending","acknowledgement
> pending","partially delivered"
>
> }'::text[])) OR ((order_status)::text = 'dispatched'::text) OR
> ((order_status)::text = 'partially dispatched'::text))
>
> Rows Removed by
> Filter: 0
>
> Heap Blocks:
> exact=41645
>
> -> BitmapAnd
> (cost=76.47..76.47 rows=19 width=0) (actual time=6.027..6.027 rows=0
> loops=221068)
>
> -> Bitmap
> Index Scan on dbr_code_128932 (cost=0.00..37.89 rows=3756 width=0) (actual
> time=5.054..5.054
>
> rows=42741 loops=221068)
>
> Index
> Cond: ((dbr_code)::text = '304717'::text)
>
> -> Bitmap
> Index Scan on retailer_code_128923 (cost=0.00..38.29 rows=3756 width=0)
> (actual time=0.014..
>
> 0.014 rows=28 loops=221068)
>
> Index
> Cond: ((retailer_code)::text = (retailerlist.retailer_code)::text)
>
> -> Index Scan using
> order_no_128903 on torder_delivery_note orderdeliverynote
> (cost=0.55..307.20 rows=6799 width=424) (a
>
> ctual time=0.014..0.014 rows=1 loops=36615)
>
> Index Cond:
> ((order_no)::text = ("Order".order_no)::text)
>
> -> Materialize (cost=0.42..49.41
> rows=2 width=172) (actual time=0.001..0.001 rows=0 loops=36615)
>
> -> Index Scan using
> role_1239084_idx on tleave leave (cost=0.42..49.40 rows=2 width=172)
> (actual time=46.270..46.270 row
>
> s=0 loops=1)
>
> Index Cond:
> ((role)::text = 'DB'::text)
>
> Filter: (date =
> '2021-06-15'::date)
>
> Rows Removed by Filter:
> 144656
>
> -> Index Scan using username_12891 on
> tuser_mst dse_user (cost=0.42..0.44 rows=1 width=19) (actual
> time=0.005..0.005 rows=1 loops=36
>
> 615)
>
> Index Cond: ((username)::text =
> (orderdeliverynote.assigned_to_username)::text)
>
> Planning Time: 2.019 ms
>
> Execution Time: 1365688.026 ms
>
> (38 rows)
>
>
>
>
>
>
>
> There are bad estimations - did you run ANALYZE?
>
>
>
> Pavel
>
>
>
>
>
>
>
> *Query:*
>
>
>
>
>
> EXPLAIN (ANALYZE) select
> DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
>
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
>
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no,
>
> CASE WHEN "Order"."order_status"='dispatched' then 3
>
> WHEN "Order"."order_status"='partially dispatched' then 3
>
> WHEN "Order"."order_status"='acknowledgement pending' then 4
>
> WHEN "Order"."order_status"='delivered' then 2
>
> WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
> "Order__actionable"
>
> from torder "Order"
>
> join tretailer_mst as RetailerList on
> "Order".retailer_code=RetailerList.retailer_code
>
> join torder_delivery_note as OrderDeliveryNote on
> "Order".order_no=OrderDeliveryNote.order_no
>
> left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
> leave.date='2021-06-15' and leave.role='DB'
>
> join tuser_mst DSE_user on
> OrderDeliveryNote.assigned_to_username=DSE_user.username
>
> where dbr_code='304717' AND (order_status IN (('delivered'),('dn
> pending'),('acknowledgement pending'),
>
> ('partially delivered')) or ((order_status = 'dispatched' or order_status
> = 'partially dispatched') and leave.date = '2021-06-15'))
>
> group by "Order".order_no
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
>
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
>
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
>
> order by OrderDeliveryNote.date desc limit 5;
>
>
>
> please, try to run ANALYZE statement first, and then repeat EXPLAIN ANALYZE
please, don't send screenshots - use explain.depesz.com application for
sharing plans
Regards
Pavel
>
>
>
>
> Regards,
>
> Ram Pratap.
>
>
>
> *From:* Pavel Stehule [mailto:pavel.stehule@gmail.com]
> *Sent:* 16 June 2021 00:28
> *To:* Ram Pratap Maurya; PostgreSQL mailing lists
> *Subject:* Re: BUG #17059: postgresql 13 version problem related to query.
>
>
>
>
>
>
>
> út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <
> noreply@postgresql.org> napsal:
>
> The following bug has been logged on the website:
>
> Bug reference: 17059
> Logged by: Ram Pratatp maurya
> Email address: ram.maurya@lavainternational.in
> PostgreSQL version: 13.0
> Operating system: RHEL 8.3
> Description:
>
> Hi team,
>
> I am facing problem related to query.
>
> I have two DB server one is running on postgres12 (RHEL 6) and second is
> running on postgresql -13 (RHEL 8.3).
> Server H/W configuration is same and postgresql.conf file configuration
> parameter is also same.
>
> When I am running below queary on server postgresql-12 data comes within 2
> min and when I run this quear on postgresql-13 server its take 30 min
> display result.
>
> Can you please suggest any problem in postgresql-13.
>
> Note : DB size of postgresql-12 is 242 GB (1 year old data) and DB size of
> postgresql-12 is 350 GB.
>
>
> .........................................................................................................................................................................................................
> select
>
> DISTINCT("Order".order_no),"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no,
> CASE WHEN "Order"."order_status"='dispatched' then 3
> WHEN "Order"."order_status"='partially dispatched' then 3
> WHEN "Order"."order_status"='acknowledgement pending' then 4
> WHEN "Order"."order_status"='delivered' then 2
> WHEN "Order"."order_status"='dn pending' then 1 ELSE 0 END AS
> "Order__actionable"
> from torder "Order"
> join tretailer_mst as RetailerList on
> "Order".retailer_code=RetailerList.retailer_code
> join torder_delivery_note as OrderDeliveryNote on
> "Order".order_no=OrderDeliveryNote.order_no
> left join tleave leave on "Order".order_no=OrderDeliveryNote.order_no and
> leave.date='2021-06-15' and leave.role='DB'
> join tuser_mst DSE_user on
> OrderDeliveryNote.assigned_to_username=DSE_user.username
> where dbr_code='304717' AND (order_status IN (('delivered'),('dn
> pending'),('acknowledgement pending'),
> ('partially delivered')) or ((order_status = 'dispatched' or order_status =
> 'partially dispatched') and leave.date = '2021-06-15'))
> group by "Order".order_no
>
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
> order by OrderDeliveryNote.date desc limit 50;
>
>
>
> please, send result of explain analyze for pg 12 and pg 13
>
>
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> https://explain.depesz.com/
>
>
>
> Regards
>
>
>
> Pavel
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-15 20:14 Ram Pratap Maurya <ram.maurya@lavainternational.in>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Ram Pratap Maurya @ 2021-06-15 20:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Dear Tom,
Postgresql-12 have one year old data compare than postgresql-13 (postgresql-12 is QRD server and postgresql-13 is test server)
And same query we run is PRD postgresql-11 server data will come within 2-3 min.
Regards,
Ram Pratap.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 June 2021 01:25
To: Pavel Stehule
Cc: Ram Pratap Maurya; PostgreSQL mailing lists
Subject: Re: BUG #17059: postgresql 13 version problem related to query.
Pavel Stehule <pavel.stehule@gmail.com> writes:
> út 15. 6. 2021 v 21:36 odesílatel Ram Pratap Maurya <
> ram.maurya@lavainternational.in> napsal:
>> Postgresql-12 :
>> -> Index Scan using retailer_code_12390127_idx
>> on tretailer_mst retailerlist (cost=0.42..5468.67 rows=146923
>> width=25) (actual time=0.018..180.502 rows=144211 loops=1)
>>
>> Postgresql-13
>> -> Index Scan using
>> retailer_code_12390127_idx on tretailer_mst retailerlist
>> (cost=0.38..0.59
>> rows=1 width=684) (actual time=0.028..928.347 rows=221068 loops=1)
There seem to be rather serious discrepancies between rowcount estimates and reality on the v13 installation. Maybe an ANALYZE would help.
I'm also wondering why auto-analyze hasn't come along and improved the situation.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-21 23:54 Bruce Momjian <bruce@momjian.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2021-06-21 23:54 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Ram Pratap Maurya <ram.maurya@lavainternational.in>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jun 15, 2021 at 10:02:45PM +0200, Pavel Stehule wrote:
> group by "Order".order_no
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> OrderDeliveryNote.assigned_to_role,
>
> OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> OrderDeliveryNote.total_value,
>
> leave.date, leave.username, RetailerList.retailer_outlet,
> DSE_user.first_name, "Order".parent_order_no
>
> order by OrderDeliveryNote.date desc limit 5;
>
>
>
> please, try to run ANALYZE statement first, and then repeat EXPLAIN ANALYZE
>
> please, don't send screenshots - use explain.depesz.com application for sharing
> plans
Pavel, on my laptop, I count 18 screen fulls of the previous email
before I got to your reply. Wouldn't trimming the original email make
sense here?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17059: postgresql 13 version problem related to query.
@ 2021-06-22 04:04 Pavel Stehule <pavel.stehule@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 10+ messages in thread
From: Pavel Stehule @ 2021-06-22 04:04 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Ram Pratap Maurya <ram.maurya@lavainternational.in>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
út 22. 6. 2021 v 1:54 odesílatel Bruce Momjian <bruce@momjian.us> napsal:
> On Tue, Jun 15, 2021 at 10:02:45PM +0200, Pavel Stehule wrote:
> > group by "Order".order_no
> >
> ,"Order".retailer_code,"Order".order_status,OrderDeliveryNote.assigned_to_username,
> > OrderDeliveryNote.assigned_to_role,
> >
> > OrderDeliveryNote.delivery_note_no, OrderDeliveryNote.date,
> > OrderDeliveryNote.total_value,
> >
> > leave.date, leave.username, RetailerList.retailer_outlet,
> > DSE_user.first_name, "Order".parent_order_no
> >
> > order by OrderDeliveryNote.date desc limit 5;
> >
> >
> >
> > please, try to run ANALYZE statement first, and then repeat EXPLAIN
> ANALYZE
> >
> > please, don't send screenshots - use explain.depesz.com application for
> sharing
> > plans
>
> Pavel, on my laptop, I count 18 screen fulls of the previous email
> before I got to your reply. Wouldn't trimming the original email make
> sense here?
>
yes. I am sorry
Pavel
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-06-22 04:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-15 18:31 BUG #17059: postgresql 13 version problem related to query. PG Bug reporting form <noreply@postgresql.org>
2021-06-15 18:57 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-06-15 19:36 ` Ram Pratap Maurya <ram.maurya@lavainternational.in>
2021-06-15 19:42 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-06-15 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-15 20:14 ` Ram Pratap Maurya <ram.maurya@lavainternational.in>
2021-06-15 20:00 ` Ram Pratap Maurya <ram.maurya@lavainternational.in>
2021-06-15 20:02 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-06-21 23:54 ` Bruce Momjian <bruce@momjian.us>
2021-06-22 04:04 ` Pavel Stehule <pavel.stehule@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