Thread: BUG #17059: postgresql 13 version problem related to query.

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;

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> 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

 

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;

 

 

 

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

 

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.