[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Any advice tuning this query ?
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2016-11-12 19:25:34
Message-ID: CAMkU=1wifZ_AHxv-KSrnvO0P8QzXk1TMQZKV-mypV_wu+qX_aw () mail ! gmail ! com
[Download RAW message or body]

On Fri, Nov 11, 2016 at 7:19 AM, Henrik Ekenberg <henrik@ekenberg.pw> wrote:

> Hi,
>
> I have a select moving around a lot of data and takes times
> Any advice tuning this query ?
>
> EXPLAIN (ANALYZE ON, BUFFERS ON)
>

When accessing lots of data, sometimes the act of collecting timing on all
of the actions makes the query take >2x times longer, or more, and distorts
the timings it collects.

Try running the same query like:

EXPLAIN (ANALYZE ON, BUFFERS ON, timing off)

If the Execution times are very similar either way, then you don't have
this problem.  But if they differ, then you can't depend on the results of
the timings reported when timing is turned on.  Large sorts are
particularly subject to this problem.

More than half the time (if the times are believable) goes to scanning the
index activations_books_date.  You might be better off with a sort rather
than an index scan.  You can test this by doing:

begin;
drop index activations_books_date;
<explain your query here>;
rollback;

Don't do that on production server, as it will block other access to the
table for the duration.


You might also benefit from hash joins/aggregates, but you would have to
set work_mem to a very large value get them.  I'd start by setting work_mem
in your session to 1TB, and seeing if that changes the explain plan (just
explain, not explain analyze!).  If that supports the hash
joins/aggregates, then keeping lowering work_mem until you find the minimum
that supports the hash plans.  Then ponder if it is safe to use that much
work_mem "for real" given your RAM and level  of concurrent access.

Cheers,

Jeff

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Fri, Nov 11, 2016 \
at 7:19 AM, Henrik Ekenberg <span dir="ltr">&lt;<a href="mailto:henrik@ekenberg.pw" \
target="_blank">henrik@ekenberg.pw</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><u></u>





<div style="font-family:arial;font-size:14px">
<p>Hi,<br>
<br>
I have a select moving around a lot of data and takes times<br>
Any advice tuning this query ?<br>
<br>
EXPLAIN (ANALYZE ON, BUFFERS ON)<br></p></div></blockquote><div><br></div>When \
accessing lots of data, sometimes the act of collecting timing on all of the actions \
makes the query take &gt;2x times longer, or more, and distorts the timings it \
collects.<br><br>Try running the same query like:<br><br>EXPLAIN (ANALYZE ON, BUFFERS \
ON, timing off)<br><br>If the Execution times are very similar either way, then you \
don&#39;t have this problem.   But if they differ, then you can&#39;t depend on the \
results of the timings reported when timing is turned on.   Large sorts are \
particularly subject to this problem.<br><br>More than half the time (if the times \
are believable) goes to scanning the index activations_books_date.   You might be \
better off with a sort rather than an index scan.   You can test this by \
doing:</div><div class="gmail_quote"><br></div><div \
class="gmail_quote">begin;</div><div class="gmail_quote">drop index \
activations_books_date;</div><div class="gmail_quote">&lt;explain your query \
here&gt;;</div><div class="gmail_quote">rollback;</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">Don&#39;t do that on \
production server, as it will block other access to the table for the \
duration.</div><div class="gmail_quote"><br></div><div \
class="gmail_quote"><br></div><div class="gmail_quote">You might also benefit from \
hash joins/aggregates, but you would have to set work_mem to a very large value get \
them.   I&#39;d start by setting work_mem in your session to 1TB, and seeing if that \
changes the explain plan (just explain, not explain analyze!).   If that supports the \
hash joins/aggregates, then keeping lowering work_mem until you find the minimum that \
supports the hash plans.   Then ponder if it is safe to use that much work_mem \
&quot;for real&quot; given your RAM and level   of concurrent access.</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">Cheers,</div><div \
class="gmail_quote"><br></div><div class="gmail_quote">Jeff</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]