Large query optimization and expected time

[smarmet]

I have a single table query returning about 460,000 rows from a MSSQL server. Using tsql, the query begins returning rows in 1-3s, and completes in roughly a minute. Using tds_fdw, I see 15-40 minutes to complete the same query. Smaller queries, or more selective queries on the same table, will be faster in rough proportion to the number of rows returned.

I am not sure where to begin troubleshooting this. I see no resource constraints on the server system, nor any errors in either the postgres or TDS logs. Are there any configuration options I should look into, similar to postgres_fdw's fetch_size? Are my expectations simply off, and I would do better to pull this data via script? Can someone provide an example of how long querying half a million rows should take via tds_fdw?

[GeoffMontee]

Hi @smarmet,

Some notes:

1.) tds_fdw can take too long to plan queries with row_estimate_method=execute. Try setting row_estimate_method=showplan_all for the table. Or you can disable remote estimates during planning by setting use_remote_estimate=0.

2.) tds_fdw will only push down the where clause to the remote server if table_name is used, rather than query. match_column_names=1 is also a requirement, but I believe that is set by default when table_name is specified.

If that does not help, can you please provide the table definition and the query?

[smarmet]

Thanks for the quick turnaround.

I am already using showplan_all, planning times reported by Explain/Analyze are ~9s, so that doesn't look like a concern. I believe where clauses are propagating correctly, as more restrictive queries do return faster. I will test with match_column_names explicitly set anyway.

Unfortunately, the machine running the query is on an isolated network, so I can't just copy/paste exact queries and definitions. The table in question has only 4 columns, 3 integers and a varchar. One of the integer columns is an id for the table, the other two are foreign keys supporting many/many relationships. I am filtering on one of those foreign keys. My query is SELECT * FROM HardwareValue_MT WHERE HardwarePropertyID in (3, 5, 11, 13)

[GeoffMontee]

I believe where clauses are propagating correctly, as more restrictive queries do return faster. 

We can confirm that WHERE pushdown is occurring by setting the log level to DEBUG3, so that tds_fdw prints the query:

https://github.com/tds-fdw/tds_fdw/blob/master/src/tds_fdw.c#L403

If you set client_min_messages to DEBUG3, then the messages would appear in the client.

If you set log_min_messages to DEBUG3, then the messages would appear in the log.

[smarmet]

The original version of this comment had some major errors from playing telephone; please disregard if you saw it.

With client_min_messages=DEBUG3, I do see the query including the WHERE as appropriate.

One one run of the query I got a series of warnings suggesting that none of the column names could be matched, but I now believe this was due to a restart on the MSSQL end. match_column_names is set to 1, and no further warnings appear.

[GeoffMontee]

WARNING: Table definition mismatch: Could not match local column "Value" with column from foreign table.

If I remember correctly, I don't think this warning always indicates a problem with the column. I think this warning also appears if a column is not selected by a query.

For example, if a foreign table ftab has col1, col2, and col3, let's say that the following query is executed:

SELECT col1, col2 FROM ftab;

In this case, I think you would see a warning like this, since col3 is not included in the select statement:

WARNING: Table definition mismatch: Could not match local column "col3" with column from foreign table.

Ideally, the check for this warning should be improved eventually.

Regardless, I am not sure why you only saw 3 messages. tds_fdw writes a lot of messages when DEBUG3 is the message level.

[smarmet]

I think I cross posted an edit. For clarity, the DEBUG3 did produce quite a few messages, including a full query including the where clause. The warnings are also gone, I think they are related to some server maintenance on the MSSQL end

[GeoffMontee]

Oh, I see. Sorry for the confusion.

It's good to know that the query was printed, and that the query did include the WHERE clause.

So far, it sounds like we know these details:

• row_estimate_method=showplan_all is set, so query planning should be quick.
• WHERE pushdown is occurring, so the remote server should be filtering the results.
• The query is still slow, for some reason.

Some more questions:

• Do you have any metrics about system resources during query execution? Does PostgreSQL's memory usage grow quickly during the query? Does PostgreSQL swap a lot during the query? Does PostgreSQL use a lot of CPU during the query? Conversely, does the system seem way too idle for large chunks of time during the query?
• If you increase work_mem, does that change anything?

[smarmet]

The confusion was most certainly on my end, no need to apologize.

There doesn't seem to be any crunch on resources, though I neglected to look at swap. Unfortunately, a maintenance period in the source MSSQL server means I won't be able to do more troubleshooting until Monday.

Thank you for your attentive replies!

[smarmet]

work_mem was set to 4MB, which I think is a default. I set it to 64MB but did not see any change. Selecting ~50,000 rows into a table resulted in a table size of 7MB. Interestingly, inserting the rows into a new table didn't take any more time than simply selecting them.

Just to validate my expectations, how long should a query of 50K rows take? Should the full 460,000 take 10X longer?