Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-622

Runaway query when searching for Available + Book Items

    XMLWordPrintable

Details

    • Prokopovych
    • R1 2021
    • GBV

    Description

      Overview:
      When searching for Items that are Available and of (material) type Book, the query that is invoked in the back end runs for at least 40 minutes and consumes lots CPU.

      Steps to Reproduce:

      1. Go to Inventory app
      2. Select "Available" in the Item Status dropdown list
      3. Select "book" in the Material type dropdown list

      Expected Results:
      Results coming back quickly, within seconds at the most
      Actual Results:
      No results coming back. The database gets busy querying for 40 minutes and sometimes the database's CPU utilization spiked to as high as 90% . See the blue lines in this graph:
      Additional Information:
      Reproducible in Bugfest and PTF environments
      CQL Query:

      /instance-storage/instances?query=(item.materialTypeId=="025ba2c5-5e96-4667-a677-8186463aee69"+and+item.status.name=="Available")+sortby+title&limit=100&offset=0
      

      SQL Query:

      WITH  headrecords AS (   
      	SELECT jsonb, (lower(f_unaccent(jsonb->>'title')) ) AS data_column FROM fs09000000_mod_inventory_storage.instance   
      	WHERE ((instance.id IN  ( 
      		SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  ( 
      			SELECT holdingsRecordId FROM item WHERE materialTypeId='025ba2c5-5e96-4667-a677-8186463aee69'))) 
      		AND (instance.id IN  ( 
      			SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  ( 
      				SELECT holdingsRecordId FROM item WHERE lower(f_unaccent(item.jsonb->'status'->>'name')) 
      				LIKE lower(f_unaccent('Available'))))))     
      	AND left(lower(f_unaccent(jsonb->>'title')) ,600) < ( 
      		SELECT left(lower(f_unaccent(jsonb->>'title')) ,600)
      		FROM fs09000000_mod_inventory_storage.instance
      		ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600)                
      		OFFSET 10000 LIMIT 1
      		)   
      	ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600)    
      		LIMIT 100 OFFSET 0 ),  
      	
      	allrecords AS (   
      		SELECT jsonb, lower(f_unaccent(jsonb->>'title'))  AS data_column FROM fs09000000_mod_inventory_storage.instance   
      		WHERE ((instance.id IN  ( 
      			SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  ( 
      				SELECT holdingsRecordId FROM item WHERE materialTypeId='025ba2c5-5e96-4667-a677-8186463aee69'))) 
      			AND (instance.id IN  ( 
      				SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  ( 
      					SELECT holdingsRecordId FROM item WHERE lower(f_unaccent(item.jsonb->'status'->>'name')) 
      					LIKE lower(f_unaccent('Available'))))))     
      		AND (SELECT COUNT(*) FROM headrecords) < 100 ), 
      	
      	totalCount AS (
      		SELECT fs09000000_mod_inventory_storage.count_estimate('  
      			SELECT lower(f_unaccent(jsonb->>''title''))  AS data_column   
      			FROM fs09000000_mod_inventory_storage.instance
      			WHERE (instance.id IN( 
      				SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  (
      					SELECT holdingsRecordId FROM item WHERE materialTypeId=''025ba2c5-5e96-4667-a677-8186463aee69''))) 
      			AND (instance.id IN  ( 
      				SELECT instanceId FROM holdings_record WHERE holdings_record.id IN  ( 
      					SELECT holdingsRecordId 
      					FROM item 
      					WHERE lower(f_unaccent(item.jsonb->''status''->>''name'')) 
      					LIKE lower(f_unaccent(''Available'')))))') AS count) 
      	
      	SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM headrecords
      	WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION (
      		SELECT jsonb, data_column, (SELECT count FROM totalCount)
      		FROM allrecords
      		ORDER BY data_column
      		LIMIT 100 OFFSET 0 ) 
      	ORDER BY data_column
      

      Interested parties: julianladisch marcjohnson cboerema Emelianov

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                Unassigned Unassigned
                mtraneis Martin Tran
                Votes:
                0 Vote for this issue
                Watchers:
                11 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases