On 2/27/22 18:20, Jeff Janes wrote: > > On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx > <mailto:MKumar@xxxxxxxxxxxxxxxxx>> 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. > I'd bet Jeff is right and poor estimates are the root cause. The pattern with a cascade of "nested loop" in the explain is fairly typical. This is likely due to the complex join conditions and correlation. > 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). > Right, simplify the query. Or maybe do it the other way around - start with the simplest query (the inner-most part of the explain) and add joins one by one (by following the explains) until it suddenly starts being much slower. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
