Connection-level qualifiers for faster queries and lower API load

Steampipe now accelerates queries that involve only one of many aggregated connections. Here's a query of ~250,000 SQS queues spread across 10 AWS accounts before the new optimization:

select count(*) from aws_sqs_queue where account_id = '767398119043'

Time: 18.4s. Rows returned: 1. Rows fetched: 248,456. Hydrate calls: 248,456. Scans: 10. Connections: 10.

Although the 767398119043 account is the only one of interest, Steampipe didn't know that, so it queried all 10 connections. Those subqueries run in parallel, and return a huge amount of data in just 18 seconds! But the query would be even faster, and lighter on API load, if Steampipe only needed to fetch rows for the target account.

Here are stats for the same query using v0.23.0 and the latest AWS plugin, which includes support for connection-level qualifiers:

Time: 2.3s. Rows returned: 1. Rows fetched: 8,500. Hydrate calls: 8,500.

In this case it's 8 times faster and fetches 96.5% fewer rows! The query skips nine irrelevant connections, targets the only one of interest, fetches vastly fewer rows and finishes in a few seconds.

New .verbose timing

To see these results in more detail, we'll turn on another new feature, verbose timing. Here's the verbose view without connection qualifers, showing that Steampipe fetches hundreds of thousands of rows that the query didn't require.

Time: 17.4s. Rows returned: 8,500. Rows fetched: 248,456. Hydrate calls: 0. Scans: 10. Connections: 10.

Scans:
  1) aws_sqs_queue.aws_pipeling_001: Time: 10.3s. Fetched: 119,209. Hydrates: 0. Quals: account_id=767398119043.
  2) aws_sqs_queue.aws_pipeling_002: Time: 6.6s. Fetched: 35,669. Hydrates: 0. Quals: account_id=767398119043.
  3) aws_sqs_queue.aws_pipeling_003: Time: 6.3s. Fetched: 34,070. Hydrates: 0. Quals: account_id=767398119043.
  4) aws_sqs_queue.aws_pipeling_005: Time: 3.9s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.
  5) aws_sqs_queue.aws_pipeling_010: Time: 3.9s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.
  6) aws_sqs_queue.aws_pipeling_009: Time: 3.9s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.
  7) aws_sqs_queue.aws_pipeling_008: Time: 3.9s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.
  8) aws_sqs_queue.aws_pipeling_007: Time: 3.9s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.
  9) aws_sqs_queue.aws_pipeling_004: Time: 3.9s. Fetched: 8,498. Hydrates: 0. Quals: account_id=767398119043.
  10) aws_sqs_queue.aws_pipeling_006: Time: 3.8s. Fetched: 8,500. Hydrates: 0. Quals: account_id=767398119043.

With connection qualifiers in play, we can see that Steampipe targets the relevant connection with surgical precision.

Time: 3.4s. Rows returned: 1. Rows fetched: 8,500. Hydrate calls: 8,500.

Scans:
  1) aws_sqs_queue.aws_pipeling_004: Time: 3.4s. Fetched: 8,500. Hydrates: 8,500. Quals: account_id=767398119043.

Steampipe was already fast, even when fetching tons more data than necessary. Now that it can fetch only what's required for a connection-qualified query, it's blindingly fast.

Plugins and mods updated to use connection qualifiers

So far we've updated the AWS, Azure, GCP, OCI, AliCloud, Kubernetes, AzureAD, and Microsoft365 plugins with support for connection qualifiers, and we'll continue to roll out these updates across the suite.

And we've updated the following mods to take advantage of the updated plugins: AWS Insights, Azure Insights, GCP Insights, OCI Insights, and Kubernetes Insights. Insight mods, particularly detail screens and relationship graphs, see huge performance gains from these changes since they start from a single resource in a single account - watch the demo video below to see it in action.

Steampipe's journey of optimization

This latest enhancement builds on a series of optimizations that have made Steampipe ever faster and more capable. v0.21.0 added rate limiters which can speed up a query that triggers excessive throttling. v0.20.0 made startup much faster. v0.16.0 introduced a new architecture that made the time to first row much faster for environments that aggregate many connections.

What's next? It's hard to say, but every time we think we've gotten to the end of the optimization journey we find new ways to make it faster. Meanwhile, enjoy the latest speedup and let us know how it goes.

See it in action