0

I have recently deployed PostgreSQL database on Linux server.
One of the stored procedure is taking around 20 to 24second.I have executed same stored procedure in blank database as well(no any row return) and it is taking same time. I found that slowness occurs because of aggregate function.
Here if i removed function ARRAY_TO_JSON(ARRAY_AGG( then result will be fetch within second.

Below is my code snippet:

 SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(A))) FROM (
 select billservice.billheaderid,billservice.billserviceid AS billserviceid,.....(around 120 columns in select ).....
 )A;


Explain Execution Plan:

enter image description here

Previously i was deployed PostgreSQL database to windows server and the same stored procedure is taking around only 1 to 1.5 second. In both cases i have tested with same database with same amount of data. and also both server have same configuration like RAM, Processor. And also have same PostgreSQL configuration. While executing my stored procedure in Linux server CPU usages goes to 100%.
Let me know if you have any solution for the same.

Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
  • 1
    Can you run `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` for the statement and add that to the question **as text**? – Laurenz Albe Jan 29 '20 at 07:08
  • added as link document – Nayan Rudani Jan 29 '20 at 08:19
  • 1
    Please don't link to some external sites. Add the execution plan generated using `explain (analyze, buffers, format text)` as [formatted text](http://stackoverflow.com/help/formatting) and make sure you prevent the indention of the plan. [edit] your question, then paste the text, then put `\`\`\`` on the line before the plan and on a line after the plan. –  Jan 29 '20 at 08:20
  • Besides, the link doesn't work. – Laurenz Albe Jan 29 '20 at 08:23
  • execution plan is too big(736 line). I can't attach with question. – Nayan Rudani Jan 29 '20 at 08:31
  • Then upload it to https://explain.depesz.com/ (but the screenshot - from what I can see - does not show a plan with over 700 lines) –  Jan 29 '20 at 08:36
  • What happens if you replace `ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(A)))` with `json_agg(ROW_TO_JSON(A))` –  Jan 29 '20 at 08:57
  • No any improvement after using json_agg(ROW_TO_JSON(A)) – Nayan Rudani Jan 29 '20 at 09:01
  • Execution Plan : https://explain.depesz.com/s/cYMW – Nayan Rudani Jan 29 '20 at 09:05

0 Answers0