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

List:       pgsql-performance
Subject:    Re: Slow Query
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-10-15 4:51:47
Message-ID: CAHOFxGo8ieq5MSi8z3BUVfH3rpvr=QPSO69jHJ00VYiaYrPv3Q () mail ! gmail ! com
[Download RAW message or body]

Based on the execution plan, it looks like the part that takes 13 seconds
of the total 14.4 seconds is just calculating the max time used in the
where clause. Anytime I see an OR involved in a plan gone off the rails, I
always always check if re-writing the query some other way may be faster.
How's the plan for something like this?


WHERE message.time = greatest( *sub1.time*, *sub2.time* )

/* sub1.time */
(
select
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
WHERE
NOT message2.draft
AND participant.identity = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND thread2.spool = spool.id
)

/* sub2.time */
(
select
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
JOIN relation ON relation.to = participant.identity
AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND relation.manages = TRUE
WHERE
NOT message2.draft
AND thread2.spool = spool.id
)

>

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">Based on the execution plan, it looks like the part \
that takes 13 seconds of the total 14.4 seconds is just calculating the max time used \
in the where clause. Anytime I see an OR involved in a plan gone off the rails, I \
always always check if re-writing the query some other way may be faster. How&#39;s \
the plan for something like this?</div><div dir="ltr"><br></div><div \
dir="ltr"><br>WHERE message.time = greatest( <i>sub1.time</i>, <i>sub2.time</i> \
)<br><br>/* sub1.time */</div><div dir="ltr">(</div><div dir="ltr">select<br>MAX ( \
message2.time )<br>FROM<br>message AS message2<br>JOIN thread AS thread2 ON <a \
href="http://thread2.id">thread2.id</a> = message2.thread<br>JOIN participant ON \
participant.thread = <a href="http://thread2.id">thread2.id</a><br>WHERE<br>NOT \
message2.draft<br>AND participant.identity = \
&#39;b16690e4-a3c5-4868-945e-c2458c27a525&#39;<br>AND thread2.spool = <a \
href="http://spool.id">spool.id</a><br>)</div><div dir="ltr"><br>/* sub2.time \
*/</div><div dir="ltr">(<br>select<br>MAX ( message2.time )<br>FROM<br>message AS \
message2<br>JOIN thread AS thread2 ON <a href="http://thread2.id">thread2.id</a> = \
message2.thread<br>JOIN participant ON participant.thread = <a \
href="http://thread2.id">thread2.id</a><br>JOIN relation ON <a \
href="http://relation.to">relation.to</a> = participant.identity<br>AND relation.from \
= &#39;b16690e4-a3c5-4868-945e-c2458c27a525&#39;<br>AND relation.manages = \
TRUE<br>WHERE<br>NOT message2.draft<br>AND thread2.spool = <a \
href="http://spool.id">spool.id</a></div><div dir="ltr">)</div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> </blockquote></div>
</blockquote></div></div>



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