Postgresql Linked Server Query very Slow

Question

i have this problem:

Making queries over linked server to postgresql is very slow, very, very slow, for example:

if i run in pgAdmin this query:

select max(oldmedicionid) from tl.tlinputtable

it returns the max result in just: 246 msec

But if i run that over linked server (using sqlserver 2008), i create the dblink using an odbc to postgresql, so if i run this:

select MAX(oldmedicionid) from LINKPDATL.PDATL.tl.tlinputtable

the query give me result in 1 minute or more sometimes...

What could be my problema?, i think is not with my postgresql database, is something like the dblink is very slow,

How can i improve the performance?

What happens when you run SELECT max_oldmedicionid FROM OPENQUERY(LINKPDATL,'select max(oldmedicionid) max_oldmedicionid from PDATL.tl.tlinputtable')? — Bacon Bits
– Bacon Bits, Commented Feb 29, 2016 at 18:07
with this way works perfect, why only with openquery works fast? — Max Pinto
– Max Pinto, Commented Mar 1, 2016 at 20:46
As @RobinsTharakan's answer suggests, the aggregation is probably being performed by SQL Server. That means the query being run on the PostgreSQL server is essentially SELECT oldmedicionid FROM tl.tlinputtable, then that entire result set is sent to the MS SQL Server, and the MS SQL Server is doing the aggregation. It's just poor optimization on the part of either SQL Server or the PostgreSQL provider. OPENQUERY() allows you to control exactly what the query being used is. — Bacon Bits
– Bacon Bits, Commented Mar 1, 2016 at 21:59

Robins Tharakan · Accepted Answer · 2016-02-29 17:34:39Z

4

Identification: There are good chances that the aggregation MAX(x) in the ODBC method is being done at the client side (not at the server side). This can easily be cross-checked by seeing that doubling the row-count would approximately double the Query time as well.

Resolution: If this is among a few corner cases, you could create a VIEW that computes this on the Postgres server-side, and the ODBC pick the aggregated value.

answered Feb 29, 2016 at 17:34

Robins Tharakan

2,52122 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Linked Server Query very Slow

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related