public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Mariel Cherkassky <[email protected]>
To: [email protected]
Subject: Re: query runs for more than 24 hours!
Date: Tue, 22 Aug 2017 23:02:39 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+t6e1m0ot7PTHDOVTjVu1M5u1VH5d7p6yYm5-eqP7KnoffWNw@mail.gmail.com>
References: <CA+t6e1m0ot7PTHDOVTjVu1M5u1VH5d7p6yYm5-eqP7KnoffWNw@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: query runs for more than 24 hours!
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox