Thread: TOAST table performance problem

Hello everybody,

[...]

How I can achieve good performance?

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.

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>:

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

www.visena.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 <ayisan1096@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.

I use pgadmin3.

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


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

---------- Forwarded message ---------

Gönderen: Asya Nevra Buyuksoy <ayisan1096@gmail.com>
Date: 10 Şub 2020 Pzt, 10:51
Subject: Re: TOAST table performance problem
To: Andreas Joseph Krogh <andreas@visena.com>

I copied my data to the CSV file, yes it is very fast. However, this does not solve my problem.

After deserializing the on the front side, I want to visualize my data on the web page effectively.   

When I select my data one by one with a limit clause, the query executes 200 ms. For example, If I create a function that takes data with a loop, the execution time will be 200 ms*1000=200 sec.

 

Andreas Joseph Krogh <andreas@visena.com>, 7 Şub 2020 Cum, 18:15 tarihinde şunu yazdı:

Try \o <filename> 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 <ayisan1096@gmail.com>:

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 <andreas@visena.com>, 7 Şub 2020 Cum, 17:42 tarihinde şunu yazdı:

På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <ayisan1096@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.

What Andreas is trying to say is that it's not PostgreSQL that is slow to read the JSON, but your client app that is slow to parse it.