Queries are very slow or not returing result at all

dcdamien commented 3 months ago

Describe the bug Queries are very slow or not returing result at all.

After starting the query, counter stops at value of approx all actual issues that are returned by equivalent JQL, for instance:

⠹ Loading results: 246

In the plugin log file there are no errors, but lots of similar new lines produced:

2024-08-13 14:53:33.817 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.818 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.819 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.820 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue names output:: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeest
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Check for keyName: EXTRA_VALUE_AT_END="map[aggregateprogress:Σ Progress aggregatetimeestim
2024-08-13 14:53:33.821 UTC [DEBUG] steampipe-plugin-jira.plugin: [DEBUG] 1723560614698: Issue output:: EXTRA_VALUE_AT_END="{customfield_10188.properties,operations,versionedRepres

After waiting for over 10 minutes the counter is still on the same value and nothing happen. After stoping the query with CTRL+C, there is an error Error: execution cancelled in CLI and the result is returned.

Steampipe version (steampipe -v)

Steampipe v0.23.3

Plugin version (steampipe plugin list)

+---------------------------------------------+---------+-------------+
| Installed                                   | Version | Connections |
+---------------------------------------------+---------+-------------+
| hub.steampipe.io/plugins/turbot/jira@latest | 0.15.0  | jira        |
+---------------------------------------------+---------+-------------+

To reproduce

$ steampipe query
Welcome to Steampipe v0.23.3
For more information, type .help
> SELECT
   id,
   key,
   self,
   status,
   created,
   resolution_date,
   type
FROM
    jira_issue
WHERE project_key = 'IT' AND created >= date_trunc('month', current_date)
;
⠙ Loading results: 245

Expected behavior Result is returned in reasonable time without cancelling the operation.

ParthaI commented 3 months ago

Hello, @dcdamien,

I apologize for any inconvenience caused. I've looked into the issue, and here are a few findings from my end. Please take a look:

Steampipe tables make API calls based on the query plan generated by Postgres.
In the query you mentioned, the table first fetches all issues regardless of the created time, then manipulates the data using the date_trunc query parameter.
Fetching all the issues takes some time because the date_trunc('month', current_date) parameter is not being passed to the API.
Using date_trunc in the WHERE clause might slightly impact performance, especially if the created column is indexed, as it changes how the date is compared.
However, when I tried the query with date_trunc('month', current_date), it returned the result as 2000-01-01 00:00:00 +0000 UTC.

I have made some modifications to the query to correctly populate the date and time and to make the API call within the specified time period.

SELECT
   id,
   key,
   self,
   status,
   created,
   resolution_date,
   type
FROM
    jira_issue
WHERE project_key = 'TEST' AND created >= now() - interval '30 day'

I tested the modified query, and it seems to be working fine on my end, making the API call based on the created query parameter.

Could you please try the query and let me know if it helps?

BTW, I have raised a PR by increasing the MaxResult value to 500 per page, earlier it was 50.

Thanks!

dcdamien commented 3 months ago

@ParthaI thank you, I just tested the modified query and it works a way faster!

turbot / steampipe-plugin-jira

Queries are very slow or not returing result at all #132