Hello,

We have a very simple query (shown below) that joins two tables,
filters on a column in each table and projects the id from one of the
tables. The query takes dozens of minutes to return when run on a 4.5
GB database. We are looking for recommendations on optimizing the
query. References to documentation relevant to query optimization will
be appreciated as well.

Note that the query returns no row.

The following were measured on Firebird 2.5.0.26074 using Flamerobin 0.9.3.1870.

Please let me know if you need more information.

Thanks,

Alec

Statistics on COPY."SOURCE_ID" column index
IDX_Lhi+/ZlWWZ/FDOab6YV2Vg== is 0.018519
Statistics on PHYSICAL_COPY."IS_MARKED_DELETED" column index
IDX_Lhi+/ZlWWZ/FDOab6YV2Vg== is 0.500000

Preparing query: SELECT FIRST 1000 PHYSICAL_COPY."ID" AS COL0 FROM
PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_MARKED_DELETED" = 1) AND (COPY."SOURCE_ID" =
'fb50a346-04e7-42d3-8782-1432001c74ad')
Prepare time: 1.203s
Field #01: PHYSICAL_COPY.ID Alias:COL0 Type:STRING(38)
PLAN JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY
INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==))
Executing...
Done.
16347133 fetches, 4 marks, 262804 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 4042375 index, 4289 seq.
Delta memory: 1423880 bytes.
Total execution time: 0:30:22 (hh:mm:ss)
Script execution finished.

NOW THE SAME QUERY WITH ONE FILTER CONDITION:

Preparing query: SELECT FIRST 1000 PHYSICAL_COPY."ID" AS COL0
FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_MARKED_DELETED" = 1)
Prepare time: 2.625s
Field #01: PHYSICAL_COPY.ID Alias:COL0 Type:STRING(38)
PLAN JOIN (PHYSICAL_COPY INDEX (IDX_sKfIZDMkd26ITRo1Da5mzQ==), COPY
INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
Executing...
Done.
186 fetches, 0 marks, 50 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 24 index, 0 seq.
Delta memory: -1423168 bytes.
Total execution time: 3.297s
Script execution finished.

NOW THE SAME QUERY WITH THE OTHER FILTER CONDITION:

Preparing query: SELECT FIRST 1000 PHYSICAL_COPY."ID" AS COL0
FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (COPY."SOURCE_ID" = 'fb50a346-04e7-42d3-8782-1432001c74ad')
Prepare time: 0.094s
Field #01: PHYSICAL_COPY.ID Alias:COL0 Type:STRING(38)
PLAN JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY
INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==))
Executing...
Done.
2273 fetches, 0 marks, 1293 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 225 index, 0 seq.
Delta memory: 1422708 bytes.
Total execution time: 23.624s
Script execution finished.