Degraded PostgresSql performance when using multi-schema setup due to unnecessary queries

[TimJongsma]

Hi all,

We have been having issues for weeks using a PostgreSQL database in combination with an on premise gateway for embedded PowerBi reporting. I finally figured out the problem and it might influence many more.

When PowerBi desktop or Service refreshes the dataset it performs a select query (without filter) on the "information_schema.tables" view. This is understandable as this view contains data about the tables and views in the database. However for every table included in the report this query is repeated. This does not provide problems on a small database server but we are running a multi tenant setup using the schema functionality of PostgreSQL in combination with views which results in a really large information_schema table.

This results in long refresh times, 10 min+, high server loads and an on premise gateway which regularly crashes with stackoverflow exceptions.

Would it be possible to only run the query on the information_schema once? As it already contains all possible information? Or only query for the relevant rows? I expect this to dramatically improve performance.

(Similar behavior seems to occur when querying for constraints, but this does not seem to impact performance as much.)

With Kind regards,

Tim Jongsma

[v-qiuyu-msft]

Hi @TimJongsma,

Please turn off the option below in Power BI desktop: 

If turn it off and refresh data, there will send two queries to data source, one for schema and another for data loading. This is currently by design. 

You can refer to this article to optimize your report: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Best Regards,
Qiuyun Yu 

[TimJongsma]

Thank for your response.

Unfortunately if i turn off that option i still see multiple queries being executed.

Turning off parallel loading of tables does limit the number of queries on the information_schema to one, however this off course has a different impact on performance.

Perhaps this is connector sepcific?

With kind regards,

Tim jongsma

[v-qiuyu-msft]

Hi @TimJongsma,

I'm not very sure as I don't have environment to test this scenario now. I would suggest you create a support ticket and let the Microsoft engineer look into your environment. 

Best Regards,
Qiuyun Yu