[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Slow query in trigger function
From: Guido Niewerth <gniewerth () ocsgmbh ! com>
Date: 2015-11-03 10:58:26
Message-ID: 83359cf9670c4026bbef97cac9005f43 () EX2k13 ! ocsnet ! local
[Download RAW message or body]
These are the queries I used to get the execution planer use the index scan instead \
of the sequential scan:
IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key) => sequential scan
IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key LIMIT 1) => sequential \
scan IF NOT EXISTS (SELECT max( 1 ) FROM custom_data WHERE key = old.key) => \
sequential scan
After breaking up the code into two statements the execution planer uses the index \
scan:
result INTEGER;
SELECT 1 FROM custom_data where key = old.key INTO result;
IF result ISNULL THEN
...
END IF;
To me it looks like the execution planer does not choose the optimal strategy. Even \
small changes in the function body make the execution planer use the slow sequential \
scan.
Guido Niewerth
25 years inspired synergy
OCS Optical Control Systems GmbH
Wullener Feld 24
58454 Witten
Germany
Tel: +49 (0) 2302 95622-0
Fax: +49 (0) 2302 95622-33
Email: gniewerth@ocsgmbh.com
Web: http://www.ocsgmbh.com
HRB 8442 (Bochum) | VAT-ID: DE 124 084 990
Directors: Hans Gloeckler, Fatah Najaf, Merdan Sariboga
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.E-MailFormatvorlage17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.apple-converted-space
{mso-style-name:apple-converted-space;}
span.E-MailFormatvorlage21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">These are the queries I used to get \
the execution planer use the index scan instead of the sequential \
scan:<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key) \
=> sequential scan<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key \
LIMIT 1) => sequential scan<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">IF NOT EXISTS (SELECT max( 1 ) FROM custom_data WHERE key = \
old.key) => sequential scan<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">After breaking up the code into two statements the execution \
planer uses the index scan:<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">result INTEGER;<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D">SELECT 1 FROM custom_data where key = \
old.key INTO result;<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">IF result ISNULL THEN<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D"> ...<o:p></o:p></span></p> \
<p class="MsoNormal"><span style="color:#1F497D">END IF;<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D">To me it looks like the execution \
planer does not choose the optimal strategy. Even small changes in the function body \
make the execution planer use the slow sequential scan.<o:p></o:p></span></p> </div>
<div style="FONT-SIZE: 12pt; FONT-FAMILY: 'Arial'; TEXT-ALIGN: left">
<p>
<table style="HEIGHT: 223px; WIDTH: 392px" cellspacing="2" border="0">
<tbody>
<tr style="BORDER-TOP-COLOR: ; COLOR: ; BORDER-BOTTOM-COLOR: ; TEXT-DECORATION: ; \
BORDER-RIGHT-COLOR: ; BORDER-LEFT-COLOR: "> <td valign="top">
<p style="MARGIN-BOTTOM: 6pt; MARGIN-TOP: 6pt"><span style="FONT-FAMILY: 'Calibri'; \
FONT-WEIGHT: bold; COLOR: #666666"><font style="FONT-SIZE: 10pt" \
color="#333333">Guido</font> <font style="FONT-SIZE: 10pt" color="#333333">Niewerth \
</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'; FONT-WEIGHT: \
bold"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><br>
</span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><strong><font \
color="#666666"><font style="FONT-SIZE: 12pt" color="#ff0000">25</font> years \
inspired synergy</font></strong><br> </span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><span style="FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold; \
COLOR: #666666"><font style="FONT-SIZE: 12pt">OCS</font></span><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold"> </span><span \
style="FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold; COLOR: #ff0000"><font \
style="FONT-SIZE: 12pt">O</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'; FONT-WEIGHT: bold; COLOR: #666666">ptical<font style="FONT-SIZE: 12pt"> \
</font></span><font style="FONT-SIZE: 12pt"><span style="FONT-FAMILY: 'Calibri'; \
FONT-WEIGHT: bold; COLOR: #ff0000">C</span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'; FONT-WEIGHT: bold; COLOR: #666666">ontrol </span><span style="FONT-FAMILY: \
'Calibri'; FONT-WEIGHT: bold; COLOR: #ff0000">S</span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold; COLOR: #666666">ystems \
GmbH</span></font><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'; FONT-WEIGHT: \
bold"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><font \
color="#333333">Wullener Feld 24</font></span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">58454</font></span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">Witten</font></span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">Germany</font><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold"><br> </span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><font style="FONT-SIZE: 6pt"><br> </font></span><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"></span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><font color="#333333">Tel: </font><font \
color="#333333">+49 (0) 2302 95622-0</font> </span><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">Fax:</font> <font color="#333333">+49 (0) \
2302 95622-33</font></span></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'; FONT-WEIGHT: bold"><br> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">Email: gniewerth@ocsgmbh.com</font></span><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><br> </span><span style="FONT-SIZE: \
10pt; FONT-FAMILY: 'Calibri'"></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: \
'Calibri'"><font color="#333333">Web: http://www.ocsgmbh.com</font></span><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold"><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'; FONT-WEIGHT: bold"><br> </span><span \
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><font style="FONT-SIZE: 6pt"><br> \
</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><font \
style="FONT-SIZE: 8pt" color="#333333">HRB 8442 (Bochum) | VAT-ID: DE 124 084 \
990</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Calibri'"><br> <font \
style="FONT-SIZE: 8pt" color="#333333">Directors: Hans Gloeckler, Fatah Najaf, Merdan \
Sariboga</font></span></span></span></p> </td>
</tr>
</tbody>
</table>
<span style="FONT-SIZE: 8pt; FONT-FAMILY: 'Calibri'"><span style="FONT-SIZE: 10pt; \
FONT-FAMILY: 'Calibri'"></span></p> </span></div>
<br>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic