Optimize hydrating/retrieving data by using SOQL functions as much as possible

[d416]

Is your feature request related to a problem? Please describe.
SOQL should be used as much as possible for executing data functions before retrieving.

Describe the solution you'd like
A couple of examples...

EXAMPLE 1:
select count(*) from salesforce_opportunity

^ this query will
a) download all records from salesforce_opportunity
b) perform a SQL count() on the data in postgres

It works for a few hundred records maybe, but for data sets of thousands this takes quite awhile. SOQL has its own count() function, so why not use that and let Salesforce carry the load?

EXAMPLE 2:

select id, createddate from salesforce_account limit 10

^ this query will retrieve the first 10 records from the Account object and runs relatively quickly.

but...

select id, createddate from account order by createddate desc limit 10

^ this query will download all records from the Account object then sort them in Postgres by createddate, then return the first 10 records which can take quite awhile.
Salesforce has its own 'order by' function - why not use it and let Salesforce do the lifting here?

Describe alternatives you've considered
Waiting for real simple queries like the above to execute, or even time-out if large data is present in Salesforce.

[d416]

Many Salesforce connectors/libraries use parsers, such as mulesoft's hardened code:
https://github.com/adam84luong/salesforce-soql-parser/blob/master/src/main/antlr3/org/mule/tools/soql/parser/SOQL.g

Here is a similar one in golang...
https://github.com/tzmfreedom/soql-parser/blob/master/parser/soql_parser.go

...but then perhaps we're just bound by what simpleforce can do and this is an issue for that code base

[cbruno10]

@d416 Thanks for opening this issue!

At the moment, for queries like the one you shared above, we don't get any order by information from queries, so unfortunately we wouldn't be able to use that information to affect how we form the SOQL and make API calls.

I've opened turbot/steampipe-plugin-sdk#596 for tracking, which would enable us to do so.

I'm closing this issue in the meantime as the feature is not available, but if there are additional use cases outside of order by, please feel free to re-open!