Uploaded image for project: 'mod-source-record-manager'
  1. mod-source-record-manager
  2. MODSOURMAN-565

Query takes ~9s to complete

    XMLWordPrintable

    Details

    • Template:
    • Development Team:
      Folijet
    • Release:
      R3 2021 Bug Fix

      Description

      Leave open for now; test in Kiwi Bugfest to see if query time has decreased

      The below query takes ~9s to complete - 

      2021-09-17 18:06:23 UTC:10.23.36.66(51774):tenantId_mod_source_record_manager@folio:[20885]:LOG: duration: 9698.268 ms statement: SELECT CASE WHEN job_execution_progress.jsonb IS NULL THEN job_executions.jsonb ELSE jsonb_set(job_executions.jsonb, '{progress}', jsonb_build_object('jobExecutionId', job_execution_progress.jsonb -> 'jobExecutionId', 'total', job_execution_progress.jsonb -> 'total', 'current', (job_execution_progress.jsonb ->> 'currentlySucceeded')::int + (job_execution_progress.jsonb ->> 'currentlyFailed')::int)) END as jsonb, (SELECT COUNT(id) FROM fs00001034_mod_source_record_manager.job_executions WHERE ((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_or(f_unaccent('COMMITTED ERROR'))) ) AS total_rows FROM fs00001034_mod_source_record_manager.job_executions LEFT JOIN fs00001034_mod_source_record_manager.job_execution_progress ON job_executions.id = (job_execution_progress.jsonb ->> 'jobExecutionId')::uuid WHERE ((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_or(f_unaccent('COMMITTED ERROR'))) ORDER BY left(lower(f_unaccent(job_executions.jsonb->>'completedDate')),600) DESC, lower(f_unaccent(job_executions.jsonb->>'completedDate')) DESC LIMIT 25 OFFSET 0;
      and then there is an EXPLAIN ANALYZE query that takes the same amount of time2021-09-17 18:06:33 UTC:10.23.36.66(51774):tenantId_mod_source_record_manager@folio:[20885]:LOG: duration: 9387.083 ms statement: EXPLAIN ANALYZE SELECT
      CASE
      WHEN job_execution_progress.jsonb IS NULL THEN job_executions.jsonb
      ELSE jsonb_set(job_executions.jsonb, '{progress}', jsonb_build_object('jobExecutionId', job_execution_progress.jsonb -> 'jobExecutionId',
      'total', job_execution_progress.jsonb -> 'total',
      'current', (job_execution_progress.jsonb ->> 'currentlySucceeded')::int + (job_execution_progress.jsonb ->> 'currentlyFailed')::int))
      END as jsonb, (SELECT COUNT(id) FROM fs00001034_mod_source_record_manager.job_executions WHERE ((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_or(f_unaccent('COMMITTED ERROR'))) ) AS total_rows
      FROM fs00001034_mod_source_record_manager.job_executions
      LEFT JOIN fs00001034_mod_source_record_manager.job_execution_progress ON job_executions.id = (job_execution_progress.jsonb ->> 'jobExecutionId')::uuid
      WHERE ((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_or(f_unaccent('COMMITTED ERROR')))
      ORDER BY left(lower(f_unaccent(job_executions.jsonb->>'completedDate')),600) DESC, lower(f_unaccent(job_executions.jsonb->>'completedDate')) DESC
      LIMIT 25 OFFSET 0;

      So, both these queries put together take > 18s to execute purely at the DB level. This affects performance significantly since these are run pretty frequently by Data Import. 

      Data Volume Details:
      job_executions table - 9191 rows

      job_monitoring - 551 rows

      job_execution_source-chunks - 26461 rows

      job_execution_progress - 9137 rows

      journal_records - 1256515

      mapping_rules - 9

      source_records_state - 1823

      Actual Results:

      Data Import app is pretty slow(sub-optimal performance)

      Expected Results:

      Data Import app is at reasonable performance

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned Unassigned
                Reporter:
                sduvvuri Sobha Duvvuri
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    TestRail: Runs

                      TestRail: Cases