Subject: Re: query runs for more than 24 hours!
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>,
 pgsql-performance@postgresql.org
References: 
 <CA+t6e1m0ot7PTHDOVTjVu1M5u1VH5d7p6yYm5-eqP7KnoffWNw@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <d5bafb53-be6f-6beb-29ff-cc98c107c534@2ndquadrant.com>
Date: Tue, 22 Aug 2017 23:02:39 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1m0ot7PTHDOVTjVu1M5u1VH5d7p6yYm5-eqP7KnoffWNw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance