PostgreSQL query is taking too long to execute on LINUX server

Question

I have recently deployed PostgreSQL database to Linux server and one of the stored procedure is taking around 24 to 26 second to fetch the result. 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,.. etc.

While executing my stored procedure in Linux server CPU usages goes to 100%.

Execution Plan for Windows:

Execution Plan for Linux:

Let me know if you have any solution for the same.

Please add 1) the procedure / query, 2) sample data, 3) the table/index structure and 4) an `explain analyze` — Jim Jones, Jan 28 '20 at 11:50
Can you show the stored procedure. It can be multiple of diffrent reasons why its so slow, but i would start by checking indexes if your doing selects from tables... — Denis D., Jan 28 '20 at 11:53
That is unreadable. Please tun `EXPLAIN (ANALYZE, BUFFERS) SELECT ...` and copy and paste the output (formatted) into the question. — Laurenz Albe, Jan 28 '20 at 12:43
Same [cost configuration](https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS)? — Nick Barnes, Jan 28 '20 at 12:45
Yes same cost configuration, i did't changes anything for both server. — Nayan Rudani, Jan 28 '20 at 13:07

score 1 · Accepted Answer · answered Feb 03 '20 at 10:59

It also might be because of JIT coming into play in the Linux server and not on windows. Check if the query execution plan on the linux server includes information about JIT. If yes, check if that's the same in windows version. If not, than I suspect that is the case.

JIT might be adding more overhead, hence try changing the jit parameters like jit_above_cost, jit_inline_above_cost to appropriate values as per your system requirements or disable those completely by setting

jit=off

or

jit_above_cost = -1

Denis D. · Answer 2 · 2020-01-28T12:13:09.187

0

The culprit seems to be on

billservice.posid = pos.posid

More specificly its doing a Sequence Scan on pos table. It should be doing Index scan.

Check if you have indexes on these two fileds in the database.

edited Jan 28 '20 at 12:13

answered Jan 28 '20 at 12:08

Denis D.

138
1
9

Yes both table have indexes, – Nayan Rudani Jan 28 '20 at 12:10
Try to re-index pos table and try again. – Denis D. Jan 28 '20 at 12:13
I want to know why slowness occurs on Linux server as it is working fine in windows server. – Nayan Rudani Jan 28 '20 at 12:14
Because its doing a Sequential scan instead of Index scan on the pos table as far as i can see from Execution plan. – Denis D. Jan 28 '20 at 12:16
No any improvement seen after rebuild all indexes – Nayan Rudani Jan 28 '20 at 12:18
Then indexes are not corrupt. Do ANALYZE on all tables you are using in the stored procedure to recollect statistics of contents of the tables. It usally helps for query planer to use most efficient execution plan. – Denis D. Jan 28 '20 at 12:22
No any improvement after VACUUM analyze(for all tables) – Nayan Rudani Jan 28 '20 at 12:37
The interesting thing is that is that both the query plans are taking the seq_scan route for pos table, still it's slow only in linux. – mlakhara Jan 28 '20 at 16:44
Yes indeed, and that only on 54 rows – Denis D. Jan 28 '20 at 17:54

PostgreSQL query is taking too long to execute on LINUX server

2 Answers2