Subscribe to this thread
Home - General / All posts - PostgreSQL Connection Lag
joebocop
348 post(s)
#27-Oct-18 22:27

I have a map that contains a PostgreSQL data source. The data source is pointed at an AWS EC2 instance running:

PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

I am running Manifold 9.0.168.4 x64 on Windows 10 Pro. In my Manifold bin64 directory I have a libpq.dll file from PostgreSQL 10.5 compiled for Windows x64.

When I run a "passthrough" query (EXECUTE [[ ... ]] ON [My_Data_Source]) it takes ~70 seconds to execute the first time, and only a "blink" each subsequent time. The query returns two rows, containing two columns; a very small amount of data. From the Manifold 9 log, the initial and subsequent query execution times:

2018-10-27 14:05:37  -- Open: C:\Users\joe\Desktop\GT.map (0.032 sec)

2018-10-27 14:05:54  -- Load library: C:\Program Files\manifold-9.0.168.4-x64\bin64\libpq.dll

2018-10-27 14:07:02  -- Query: [qry-Records_by_Project_Per_Form] (67.240 sec)

2018-10-27 14:07:20  -- Query: [qry-Records_by_Project_Per_Form] (0.192 sec)

What could be the cause of that first build-up to executing the query?

Executing the same query text using DBeaver (using a jdbc driver) returns the results in a blink, similar to the non-initial run in Manifold 9.

If before I execute the query I "expand" the PostgreSQL data source, it takes around 9 seconds to enumerate and display the data source contents in the project pane. Executing the query thereafter returns in a "blink". So it seems faster to expand the datasource first, rather than execute the query "cold".

The Manifold 9 directory has been excluded from Windows Defender real-time scanning.

Thanks for any insight here.

adamw


8,204 post(s)
#29-Oct-18 13:09

Is there any effect on query execution times from the order in which you connect to the database in 9 and in DBeaver? Ie, if you reboot the system, launch DBeaver and run the query there first, does it still complete nearly immediately? And if you launch 9 after and run the query from 9 - without expanding the data source - does the query still complete in 70 seconds?

If execution times depend on which application runs first, we are likely dealing with caching in PostgreSQL client - we can look into how to try to reduce it, but in general this is out of our hands. However, if execution times do not depend on which application runs first, that looks like a discovery scan in 9 interfering with the query - something that we are way more equipped to solve.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.