Re: query runs for more than 24 hours!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux