Data Source preview does not pass LIMIT to PostgreSQL database, unable to query large tables

[JesterUK]

I'm connecting to our well established Flight database and having trouble bringing in the larger tables with many millions of rows. When looking at the SQL on the database server, you can see something like "SELECT * FROM flights ORDER BY id". So it seems that the "preview" tries to pull back the whole table and then only shows 1000 rows in the preview!

Example tables:

Aircraft_Model = 2,000 rows

Aircraft = 20,000 rows (FK to Aircraft_Model)

Flight = 600K rows per month (FK to Aircraft)

Flight_Measures = 470M rows per month (FK to Flight)

A simple query example:

Scatter Plot Flight_Measures.value where name = XXX and value > YYY and flight.date in last 12 months, grouped by Aircraft_Model.name

or

Bar Chart Count (Flight.id) grouped by Aircraft.name

Things I've tried:

• I've tried both the ODBC and native Postgres connections; neither apply LIMIT.
• ODBC - I've tried editing the Data Source Advanced options where it can auto detect the "LIMIT and OFFSET" capabilities; but this doesn't seem to change the queries

• In the DB Source, creating the Native Database Query to limit return - this works for only one table at a time and doesn't auto-identify foreign key links. While I can set the join on the filtered Flight table with the full Aircraft table in the Model View, I believe this is done in Memory (?) so I cannot push the join to the database when I remove the row limits as they have two separate source connections to the same DB. 

Q1. How can I get the preview to pass a LIMIT to the database? 

Q2. How can I see the actual SQL being executed? Currently I have to talk to my DBA each time I run a large query to try and "catch" it! I know how to get the DAX query for a visual, but this is not what the PostgreSQL database receives:

Thanks for any help; this has been a frustrating start to this project!

Chris.

[Anonymous]

Hi @JesterUK 

You might try changing the number of rows previewed in Power Query to the full dataset.

I found a document with a similar problem to yours for your reference:

Solved: How to increase the number of rows in preview at P... - Microsoft Fabric Community

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JesterUK]

Hi @Anonymous , thank you for your investigation - unfortunately this is the opposite of what I want to happen! I want to apply the limit in the SQL query that goes to the database. What you're suggesting would try to pull in the entire table; which would never work with over 400M rows in just one month!

Thanks for looking into it though, I appreciate it.