New! Filter and export controls, plus lots of new mods and plugins. →

Tips and Tricks

Select only the columns that you need.

This is a common recommendation for any SQL database, but it is especially important for Steampipe, as it can avoid making API calls to gather data that you don't want anyway. The difference in execution time varies by table and environment, but can be quite significant. For example, in a test account: select * from aws_iam_policy; took 14 seconds to execute, but this call took less than a second:

select
name,
arn,
is_aws_managed
from
aws_iam_policy;

The exception to this rule is the count aggregate function - Steampipe will optimize it, thus this call is very efficient (and also takes less than a second):

select
count(*)
from
aws_iam_policy;

Limit results with a where = clause on key columns when possible.

The Steampipe FDW can be more efficient if your query specifies the key columns exactly in a where clause.

For example:

select
*
from
aws_ec2_instance
where
instance_id = 'i-0f16e4805caddfd44';

For non-key columns, data for all rows must be collected, and then filtered. Currently, the only way to know definitively which columns are key columns is in the plugin source file.

Some tables require a where or join clause

The Steampipe database doesn't store data, it makes API calls to get data dynamically. There are times when listing ALL the elements represented by a table is impossible or prohibitively slow. In such cases, a table may require you to specify a qualifier in a where = (or join...on) clause. For example, the Github ListUsers API will enumerate ALL Github users. It is not reasonable to page through hundreds of thousands of users to find what you are looking for. Instead, Steampipe requires that you specify where login = to find the user directly, for example:

select
*
from
github_user
where
login = 'torvalds';

Alternatively, you join on the key column (login) in a where or join clause:

select
u.login,
o.login as organization,
u.name,
u.company,
u.location
from
github_user as u,
github_my_organization as o,
jsonb_array_elements_text(o.member_logins) as member_login
where
u.login = member_login;

or

select
u.login,
o.login as organization,
u.name,
u.company,
u.location
from
github_my_organization as o,
jsonb_array_elements_text(o.member_logins) as member_login
join github_user as u on u.login = member_login;