I am using Pentaho Table Input step to fetch data from a Postgresql database join. The query used takes 56 seconds to finish whereas the same query when executed using the pgadmin finish in less than 7 seconds. I collected the execution plan and could find that the execution plan running from both the tools is exactly the same except one index scan taking 40 more seconds to finish and that is the costliest DB action inside query execution.
Has anyone faced such issue in the past?
Any suggestions on connection configuration that will help to improve the performance?
The query is already in optimized and I can see the issue is with other queries as well so there is no scope to further improve the execution plan to fix the problem.
Your suggestions/recommendations on this topic is highly appreciated.
I want to clarify what you posted. You are saying the same query run though pgAdmin SQL window and the table input step do not have the same execution plan? I can honestly say I have not seen that. The query is passed along through the jdbc driver, it should not be "different".
Let's look for differences that could alter the plan and go from there.
A proud part of Hitachi Vantara
© Hitachi Vantara Corporation. All Rights Reserved.