Hello, I need help optimizing the query shown below. PROJECT-related tables contain 12 rows each while COPY-related tables contain 14K rows each. Moreover, PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filters out all but 10 joined rows.
My question is how do I change the plan to force PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filter to be applied before the join? Thanks! Preparing query: SELECT PROJECT."PRIMARY_PROJECT_CODE" AS COL0, COUNT(PHYSICAL_COPY."ID") AS COL1 FROM PROJECT_CODE_DESCRIPTOR INNER JOIN PROJECT ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID" = PROJECT."ID" INNER JOIN COPY_CLASSIFICATION ON PROJECT_CODE_DESCRIPTOR."PROJECT_CODE" = COPY_CLASSIFICATION."CLASSIFICATION_CODE" INNER JOIN COPY ON COPY_CLASSIFICATION."COPY_ID" = COPY."ID" INNER JOIN PHYSICAL_COPY ON COPY."ID" = PHYSICAL_COPY."COPY_ID" WHERE ( (PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED') and (PHYSICAL_COPY."IS_MARKED_DELETED" = 0) AND (PHYSICAL_COPY."IS_RECYCLED" = 0) ) GROUP BY PROJECT."PRIMARY_PROJECT_CODE" Prepare time: 0.004s Field #01: . Alias:COL0 Type:STRING(256) Field #02: . Alias:COL1 Type:INTEGER PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_sKfIZDMkd26ITRo1Da5mzQ==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==), PROJECT_CODE_DESCRIPTOR INDEX (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX (PK_f3m9slJ+02gL6hFClhrZvg==))) Executing... Done. 278233 fetches, 0 marks, 16305 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 69581 index, 0 seq. Delta memory: -3956 bytes. Total execution time: 1.243s Script execution finished.
