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

út 15. 6. 2021 v 20:53 odesílatel PG Bug reporting form <
noreply(at)postgresql(dot)org> napsal:

> The following bug has been logged on the website:
>
> Bug reference: 17059
> Logged by: Ram Pratatp maurya
> Email address: ram(dot)maurya(at)lavainternational(dot)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