Re: Never Ending query in PostgreSQL

On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh <MKumar(at)peabodyenergy(dot)com>
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

>