Subject: Re: TOAST table performance problem
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-performance@lists.postgresql.org
References: <VisenaEmail.24.3227de9e1532616f.1701fe0f525@tc7-visena>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <d2c78831-964e-4233-ac39-74a856bed78e@sqlexec.com>
Date: Fri, 7 Feb 2020 08:52:26 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.11
MIME-Version: 1.0
In-Reply-To: <VisenaEmail.24.3227de9e1532616f.1701fe0f525@tc7-visena>
Content-Type: multipart/alternative;
 boundary="------------5DB7248D68A78D0021734D44"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------5DB7248D68A78D0021734D44
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Yes, I would concur that this planning time and execution time do not 
take into account the network time sending the data back to the client, 
especially since your are sending back the entire contents of the table.

Regards,
Michael Vitale

Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:
> På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy 
> <ayisan1096@gmail.com <mailto:ayisan1096@gmail.com>>:
>
>     Sorry for the misunderstanding.
>     I have a table like;
>     CREATE TABLE zamazin
>     (
>       paramuser_id text,
>       paramperson_id integer,
>       paramdata json,
>       paramisdeleted boolean,
>       paramactiontime timestamp without time zone
>     )
>     paramdata row size is 110KB and over.
>     When I execute this query like;
>     *select * from zamazin*
>     it takes *600 seconds*.
>     But when analyze the query ;
>     *"Seq Scan on public.zamazin  (cost=0.00..21.77 rows=1077
>     width=49) (actual time=0.008..0.151 rows=1077 loops=1)"
>     "  Output: paramuser_id, paramperson_id, paramdata,
>     paramisdeleted, paramactiontime"
>     "  Buffers: shared hit=11"
>     "Planning time: 0.032 ms"
>     "Execution time: 0.236 ms"*
>      Why the query takes a long time, I do not understand. I
>     assume that this relates to the TOAST structure.
>
> My guess is the time is spent in the /client/ retrieving the data, not 
> in the DB itself. Are you on a slow network?
> -- 
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>


--------------5DB7248D68A78D0021734D44
Content-Type: multipart/related;
 boundary="------------CF9563D336EEFFD444E6EFDA"


--------------CF9563D336EEFFD444E6EFDA
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html theme="default-light"><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body text="#000000">Yes, I would concur that this planning time 
and execution time do not take into account the network time sending the
 data back to the client, especially since your are sending back the 
entire contents of the table.<br>
<br>
Regards,<br>
Michael Vitale<br>
<br>
<span>Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:</span><br>
<blockquote type="cite" 
cite="mid:VisenaEmail.24.3227de9e1532616f.1701fe0f525@tc7-visena">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <div>På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra 
Buyuksoy &lt;<a href="mailto:ayisan1096@gmail.com" 
moz-do-not-send="true">ayisan1096@gmail.com</a>&gt;:</div>


  <blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 
0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div dir="ltr">
<div dir="ltr">Sorry for the misunderstanding.
<div>I have a table like;</div>

<div>CREATE TABLE zamazin<br>
(<br>
  paramuser_id text,<br>
  paramperson_id integer,<br>
  paramdata json,<br>
  paramisdeleted boolean,<br>
  paramactiontime timestamp without time zone<br>
)</div>

<div>paramdata row size is 110KB and over.</div>

<div> </div>

<div>When I execute this query like;<br>
<b>select * from zamazin</b></div>

<div>it takes <b>600 seconds</b>.</div>

<div>But when analyze the query ;</div>

<div><b>"Seq Scan on public.zamazin  (cost=0.00..21.77 rows=1077 
width=49) (actual time=0.008..0.151 rows=1077 loops=1)"<br>
"  Output: paramuser_id, paramperson_id, paramdata, paramisdeleted, 
paramactiontime"<br>
"  Buffers: shared hit=11"<br>
"Planning time: 0.032 ms"<br>
"Execution time: 0.236 ms"</b></div>

<div> Why the query takes a long time, I do not understand. I 
assume that this relates to the TOAST structure.</div>
</div>
</div></blockquote>


  <div> </div>


  <div>My guess is the time is spent in the <em>client</em> retrieving 
the data, not in the DB itself. Are you on a slow network?</div>


  <div> </div>


  <div class="visena-email-signature" 
data-visena-message-id="&lt;VisenaEmail.24.3227de9e1532616f.1701fe0f525@tc7-visena&gt;">
<div>--
<div><span style="font-family:arial,helvetica,sans-serif;"><strong>Andreas
 Joseph Krogh</strong></span></div>

<div><span style="font-family:arial,helvetica,sans-serif;">CTO / Partner</span>
 - Visena AS</div>

<div><span style="font-family:arial,helvetica,sans-serif;">Mobile: +47 
909 56 963</span></div>

<div><a href="mailto:andreas@visena.com" moz-do-not-send="true">andreas@visena.com</a></div>

<div><a href="https://www.visena.com" moz-do-not-send="true">www.visena.com</a></div>

<div style="padding-top: 5px"><a href="https://www.visena.com" 
moz-do-not-send="true"><img alt="" 
src="cid:part4.C79BD8F0.0F885BA2@sqlexec.com" name="image.png"></a></div>
</div>
  </div>


  <div> </div>
</blockquote>
<br>
</body></html>

--------------CF9563D336EEFFD444E6EFDA
Content-Type: image/png;
 name="image.png"
Content-Transfer-Encoding: base64
Content-ID: <part4.C79BD8F0.0F885BA2@sqlexec.com>
Content-Disposition: inline;
 filename="image.png"

iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAE/0lEQVRoge1a0ZHiMAx1CVSw
0U9AsmcuLoESKIESKCEdUEJKoISUkM+b2buQElIC97GWkYVDYA72Z/Nm+MDWKo71/CR5MWbB
gmfwCd6fi6rri+rUgV/N2XfgV31Rnc5F1X2C99+xxgXfiA786lxU4xn85Qz+0oM/PGDfRfui
On3XWhd8E/qiOnGAz+Avv8HDM/Z/we++Y52IeHddC16EHvwhBrioxk/wnlPJuag6TZD+o2oU
IfbBT30uqvYdqQQRwRGNjuhiEetX+18g8Anea0LcpAbwNdv34OssISRRiqp99ToRceuILo7o
4hBf7n9BQAj+IAlhzHRq+At+Lwl0JcSvo7R/R30BACtLdHKILSJuX+1/QUA83ZIQUgm+As+E
2GlFCeN7SYhzUbUd+NUneN+Dr1+ZShBxGz4gx11ZejG3vff3RHSg9XrnynJ2XcIvTNkAwIqf
+4jPnH8AmOz0EBHY/z07td7tM3MRMZhFNYgAZwMf6otxjhD9R9Xk7CdfAnHPKcEiNnreIjY8
L9MH1xSuLL1FHGJauc4PtF7v1HNGbecQWw6k9E9EB+3XEp1kUABgJdcnfVqi7l6as4j1zXrE
WvjdHGLL7yPqqUauwyLWbrM58jN53RZxCESAOCdtNIFj0Iqq4/sIFchYXKrxYZIQoYVN7GdI
EV6KN3/Umy6DbIwxN6QQG2sRhySQiHtNvNzHIg582u/ZMTEE0do5+xwpApFGXl8gJHBq5O/B
ppUqhYhbS9RZok4RLCELE9YiDsH+pOcc0ZgoIHcVTIiksHxgXBOCa4scIf6A386QotaBnBqX
pMgpB2+c22yOMXhX4oysHgCwcpvNUSrDDSk2myMibmm93knyISKEsYv268rSR5XIkCI+YybN
WMRGBl4iHJZIKotY86HJ+Bn0YZNzSRfXgz/IG0suLFn+jZkmRK5bieMpIYZH6olEEdITcKMg
ihSgVKLm4LIP3bGEDYwfqQD3uptEbRD3klA6R8t13fj5kvnZIlwSLTu/2RzZN6ePnF1Qi5u0
PLsW7hz6j1+J49iBhMLRmNsUw0FPuhhFokegagdwZenliY0vopQhm9PF6SWiw6zEh+BNKY8x
KbksYi1TR3bDef6WXO0jdyy8D1PztF7vJCmmfM7NZWue3+BByj+DuxKpHJoQ2RQT/uYZQhgT
iipBAk2SaDeRLixiEzZ80MSQauIQ2+wnpIpHSWGJTvx9SppzpLBEp6lTnexHRoEkiOjwNlIY
80UM+Z1b0ilCTKaYjNo8g2ylreRNB42IDjo/S2lPupuMVEqJfooUMvUoW5Vqkk3nrmZuL27y
fWavOC28hRQSXEDK4KaEuI5rQmi1eRa5LkHn1UTChboEpdjr1hMRQUo9b3Y47dyeNcY8R4qk
bkjrlUaPSz9cJObyvCtLHwvWsBfZ+wbep3AY3k4KHVwZ+KlxWWz+L5xqMTPzMTCqA8i1mo0x
8Sa0m7Qj6viC6FFSGDPb6o6SFEG9RmNiqhwTggYySbLIsahO11plL+zeSwpZC8TAq7ZSdyWv
vLHkXBk+ez2v5xARuB+XQdYbAQCr6DsExCG2RHSInQ1fFmWePTUX7g1OkgSxwA21ijEhOCJ9
JesJqSCnCrJeErUPqD3Z5/bqobkH6psISQgd9FybuuAHINQRrQ56ritZ8IMRCTHzi6wFPwT9
R9XIf5Uv+OHglnT5Ye6CBT8I/wDYlyYSraMJeQAAAABJRU5ErkJggg==
--------------CF9563D336EEFFD444E6EFDA--

--------------5DB7248D68A78D0021734D44--