Re: TOAST table performance problem

[Andreas Joseph Krogh]

Try \o  in psql, to redirect the output to file, and prevent it from 
processing the json (ie. format it)

Den 7. februar 2020 15:59:05 CET, skrev Asya Nevra Buyuksoy 
:
>I try it, but there is no enhancement.
>I read this link is about TOAST and also its sub_links;
>https://blog.gojekengineering.com/a-toast-from-postgresql-83b83d0d0683
>When I execute this query, except JSON data like;
>SELECT paramuser_id, paramperson_id, paramisdeleted, paramactiontime
>  FROM zamazin;
>It takes 94 ms. :)
>
>
>Andreas Joseph Krogh , 7 Şub 2020 Cum, 17:42 tarihinde
>şunu yazdı:
>
>> På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
>> ayisan1...@gmail.com>:
>>
>> I use pgadmin3.
>>
>>
>> Try "psql", it has the lowest overhead (I think). pgAdmin might use time
>> presenting the results etc. which is easy to overlook.
>>
>> --
>> Andreas Joseph Krogh
>> ​
>>

-- 
Sendt fra min Android-enhet med K-9 e-post. Unnskyld min kortfattethet.

Re: TOAST table performance problem

[Asya Nevra Buyuksoy]

I try it, but there is no enhancement.
I read this link is about TOAST and also its sub_links;
https://blog.gojekengineering.com/a-toast-from-postgresql-83b83d0d0683
When I execute this query, except JSON data like;
SELECT paramuser_id, paramperson_id, paramisdeleted, paramactiontime
  FROM zamazin;
It takes 94 ms. :)


Andreas Joseph Krogh , 7 Şub 2020 Cum, 17:42 tarihinde
şunu yazdı:

> På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
> ayisan1...@gmail.com>:
>
> I use pgadmin3.
>
>
> Try "psql", it has the lowest overhead (I think). pgAdmin might use time
> presenting the results etc. which is easy to overlook.
>
> --
> Andreas Joseph Krogh
> ​
>

Re: TOAST table performance problem

[Andreas Joseph Krogh]

På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
ayisan1...@gmail.com >: 
I use pgadmin3. 

Try "psql", it has the lowest overhead (I think). pgAdmin might use time 
presenting the results etc. which is easy to overlook. 


-- 
Andreas Joseph Krogh 

Re: TOAST table performance problem

[Andreas Joseph Krogh]

På fredag 07. februar 2020 kl. 15:16:13, skrev Asya Nevra Buyuksoy <
ayisan1...@gmail.com >: 


Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM: 
På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
ayisan1...@gmail.com >: 


My guess is the time is spent in the client retrieving the data, not in the DB 
itself. Are you on a slow network? 
 It works in my local area and speed is 1 Gbps. When I use another local 
computer that has SSD disk the query execution time reduced to 12 seconds. But 
this query has to execute my local computer. 

What client are you using? 


--
 Andreas Joseph Krogh

Re: TOAST table performance problem

[Asya Nevra Buyuksoy]

>
>
> Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:
>
> På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
> ayisan1...@gmail.com>:
>
>
> My guess is the time is spent in the *client* retrieving the data, not in
> the DB itself. Are you on a slow network?
>
>It works in my local area and speed is 1 Gbps. When I use
another local computer that has SSD disk the query execution time reduced
to 12 seconds. But this query has to execute my local computer.

Re: TOAST table performance problem

[MichaelDBA]

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 
mailto:ayisan1...@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
andr...@visena.com 
www.visena.com 

Re: TOAST table performance problem

[Andreas Joseph Krogh]

På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy <
ayisan1...@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 
andr...@visena.com  
www.visena.com  
  

Re: TOAST table performance problem

[Asya Nevra Buyuksoy]

>
> Andreas Joseph Krogh , 7 Şub 2020 Cum, 16:12
> tarihinde şunu yazdı:
>
>> På fredag 07. februar 2020 kl. 14:07:28, skrev Asya Nevra Buyuksoy <
>> ayisan1...@gmail.com>:
>>
>>
>>
>> *[...]*
>>
>> *And of course you'll be encurraged to upgrade to latest version (12.1)
>> as 9.4.1 is now 5 years old..*
>>   You are right but for now I have to use this version :)
>>
> --
>> Andreas Joseph Krogh
>>
>

Re: TOAST table performance problem

[Asya Nevra Buyuksoy]

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.

png.png
(11K)


Andreas Joseph Krogh , 7 Şub 2020 Cum, 16:12 tarihinde
şunu yazdı:

> På fredag 07. februar 2020 kl. 14:07:28, skrev Asya Nevra Buyuksoy <
> ayisan1...@gmail.com>:
>
> Hello everybody,
>
> [...]
>
> How I can achieve good performance?
>
>
> Nobody here understands anything unless you show the exact query and
> schema...
>
> And of course you'll be encurraged to upgrade to latest version (12.1) as
> 9.4.1 is now 5 years old..
>
> --
> Andreas Joseph Krogh
>