Re: query runs for more than 24 hours!

On 08/22/2017 04:23 PM, Mariel Cherkassky wrote:
> Hi, I have a query that I run in my postgresql 9.6 database and it runs
> for more than 24 hours and doesnt finish.
>
> My select consist from few joins :
>

I'm sorry, but the query and plans are completely broken (wrapped in
funny ways, missing important bits. ...) I don't know what client you
use or how that happened, but I recommend attaching the information as
text files instead of pasting it into the message directly.

Regarding the query analysis - we can't really help you much without
seeing an explain analyze (that is, not just the plan and estimates, but
actual performance and row counts). That usually identifies the query
operations (scans, join, ...) causing issues.

Of course, if the query is already running for 24h and you don't know
how much longer it will take to complete, running EXPLAIN ANALYZE on it
is not very practical. The best thing you can do is break the query into
smaller parts and debugging that - start with one table, and then add
tables/conditions until the performance gets bad. Hopefully the explain
analyze on that will complete in reasonable time.

Of course, you haven't told us anything about what's happening on the
machine. It is reading a lot of data from the disks? Random or
sequential? Is it writing a lot of data into temporary files? Is it
consuming a lot of CPU? And so on.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services