Never Ending query in PostgreSQL
Hi Team, Can you please help in tunning the attached query as , i am trying to run this query and it runs for several hours and it did not give any output. I am not able to generate the explain analyze plan as well and it keeps on running for several hours and did not give output. I have attached the query and explain plan without analyze. Please help if nayone has any idea how to tune that query. Regards, Mukesh Kumar qyery.sql Description: qyery.sql "Nested Loop Left Join (cost=74158.47..94986.58 rows=2 width=347)" " -> Nested Loop (cost=74158.19..94985.45 rows=2 width=248)" "-> Nested Loop (cost=74157.77..94984.55 rows=2 width=266)" " -> Nested Loop (cost=74157.35..94983.64 rows=2 width=248)" "Join Filter: ((paybase.payment_sid_k)::text = (a.payment_sid_c)::text)" "-> Nested Loop (cost=74156.93..94983.04 rows=1 width=327)" " Join Filter: (((paygroup.doc_sid_f)::text = (paygroup_1.doc_sid_f)::text) AND ((paygroup.payment_group_sid_k)::text = (paygroup_1.payment_group_sid_k)::text) AND ((paybase.payment_sid_k)::text = (paybase_1.payment_sid_k)::text) AND ((docidassoc.land_contract_id)::text = (docidassoc_1.land_contract_id)::text))" " -> Nested Loop (cost=17409.66..34604.72 rows=1 width=250)" "Join Filter: ((paygroup.doc_sid_f)::text = (docidassoc.doc_sid_c)::text)" "-> Nested Loop (cost=17409.37..34604.37 rows=1 width=218)" " Join Filter: ((paygroup.doc_sid_f)::text = (lms_doc_acquisition_base.doc_sid_c)::text)" " -> Nested Loop (cost=14955.89..31181.19 rows=1 width=160)" "-> Nested Loop (cost=14955.75..31181.03 rows=1 width=162)" " -> Nested Loop (cost=14955.47..31180.73 rows=1 width=123)" "-> Nested Loop (cost=14955.05..31180.28 rows=1 width=86)" " Join Filter: (((itemvendorbase.payment_sid_c)::text = (lineitemacct.payment_sid_c)::text) AND (lineitem.line_item_seq_k = (lineitemacct.line_item_seq_c)::numeric))" " -> Nested Loop (cost=14954.63..31171.38 rows=18 width=64)" "-> Hash Join (cost=14954.21..29828.50 rows=2860 width=55)" " Hash Cond: (((itemvendorbase.payment_sid_c)::text = (lineitem.payment_sid_c)::text) AND ((itemvendorbase.line_item_seq_c)::numeric = lineitem.line_item_seq_k))" " -> Hash Left Join (cost=6942.01..19960.79 rows=345211 width=31)" " Hash Cond: (((itemvendorbase.vendor_number_c)::text || (itemvendorbase.vendor_suffix_c)::text) = ((so_vendor_address_base.ap_vendor_id_lf)::text || (so_vendor_address_base.ap_vendor_suffix_lf)::text))" " -> Seq Scan on lms_payment_item_vendor_base itemvendorbase (cost=0.00..7409.11 rows=345211 width=31)" " -> Hash (cost=5971.87..5971.87 rows=77611 width=10)" " -> Hash Left Join (cost=2617.45..5971.87 rows=77611 width=10)" " Hash Cond: (((so_vendor_address_base.ap_vendor_id_lf)::text || (so_vendor_address_base.ap_vendor_suffix_lf)::text) = (c.lfa1_emnfr)::text)" " -> Hash Join (cost=2306.88..4992.73 rows=77611 width=10)" " Hash Cond: ((so_vendor_address_base.vendor_sid_lf)::text = (so_vendor_base.vendor_sid_k)::text)" " -> Seq Scan on so_vendor_address_base (cost=0.00..2482.11 rows=77611 width=28)" " -> Hash (cost=1517.50..1517.50 rows=63150 width=17)" " -> Seq Scan on so_vendor_base (cost=0.00..1517.50 rows=63150 width=17)" " -> Hash (cost=202.48..202.48 rows=8648 width
Re: Never Ending query in PostgreSQL
Hi, On Sun, Feb 27, 2022 at 04:40:16AM +, Kumar, Mukesh wrote: > > Can you please help in tunning the attached query as , i am trying to run > this query and it runs for several hours and it did not give any output. > > I am not able to generate the explain analyze plan as well and it keeps on > running for several hours and did not give output. > > I have attached the query and explain plan without analyze. Please help if > nayone has any idea how to tune that query. You attached the explain plan in both files. Also even if there was the query there wouldn't be enough information to be able to help, please consult https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more details.
Re: Never Ending query in PostgreSQL
On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh wrote: > Hi Team, > > Can you please help in tunning the attached query as , i am trying to run > this query and it runs for several hours and it did not give any output. > Several hours is not all that long. Without an EXPLAIN ANALYZE, we could easily spend several hours scratching our heads and still get nowhere. So unless having this running cripples the rest of your system, please queue up another one and let it go longer. But first, do an ANALYZE (and preferably a VACUUM ANALYZE) on all the tables. If you have a test db which is a recent clone of production, you could do it there so as not to slow down production. The problem is that the row estimates must be way off (otherwise, it shouldn't take long) and if that is the case, we can't use the plan to decide much of anything, since we don't trust it. In parallel you could start evicting table joins from the query to simplify it until it gets to the point where it will run, so you can then see the actual row counts. To do that it does help if you know what the intent of the query is (or for that matter, the text of the query--you attached the plan twice). Cheers, Jeff >
Re: slow query to improve performace
On Fri, Feb 25, 2022 at 3:18 PM Ayub Khan wrote:
> Hi,
>
> Could some some verify the attached query to verify the performance and
> suggest some steps to improve it, this query is created as a view. This
> view is used to get the aggregates of orders based on its current status
>
I don't see how it is possible for that query to yield that plan. For
example, what part of that query could have been transformed into this part
of the plan "order_status_code <> ALL ('{T,J,C,D}'::bpchar[])"?
Well, I suppose some of the tables used in that query could themselves be
views over the same tables? In that case, we might need to know the
definitions of those views. As well as knowing the version, and seeing the
EXPLAIN (ANALYZE, BUFFERS) for the query, run after track_io_timing is
turned on.
Cheers,
Jeff
>
