| Subject | RE: [firebird-support] Slow query because an index is not picked up during sort |
|---|---|
| Author | Leyne, Sean |
| Post date | 2012-06-03T18:53:02Z |
Alex,
BTW, your post gives us no real details in which to answer your question. We don't know what fields/columns the PLAN refers to -- the hackish names obscure those details. Further, we don't know what the selectivity of the PHYSICAL_COPY."IS_DIRTY" (the index I assume the optimizer selected).
At that said, the fact that the temp file was so much larger than the database size leads me to believe that rather than focus on the query PLAN, you need to re-think the basic query.
Your original select was:
SELECT
PHYSICAL_COPY."ID", ...
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '123431234') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
If you were to modify the query to read as follows, you would almost completely eliminate the size of the temp file, and significantly increase the performance of the query.
SELECT
PHYSICAL_COPY."ID", ...
FROM (
SELECT
FIRST (1000) PHYSICAL_COPY."ID"
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '123431234') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
) AbstractTable
INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
Sean
> We tracked down the query that generated a 10GB temp file running againstCause the optimizer decided that the "cost" of navigating that index was more than filtering the result set and then performing a sort. The optimizer is concerned about the disk IO cost of a query, as such navigating an index is very expensive since rows are generally stored in index order so, reading each row from different areas of disk is very expensive.
> a 1.5GB database. Can anybody explain why the query is not using an index
> on PHYSICAL_COPY."COMMIT_NUMBER"?
BTW, your post gives us no real details in which to answer your question. We don't know what fields/columns the PLAN refers to -- the hackish names obscure those details. Further, we don't know what the selectivity of the PHYSICAL_COPY."IS_DIRTY" (the index I assume the optimizer selected).
At that said, the fact that the temp file was so much larger than the database size leads me to believe that rather than focus on the query PLAN, you need to re-think the basic query.
Your original select was:
SELECT
PHYSICAL_COPY."ID", ...
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '123431234') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
If you were to modify the query to read as follows, you would almost completely eliminate the size of the temp file, and significantly increase the performance of the query.
SELECT
PHYSICAL_COPY."ID", ...
FROM (
SELECT
FIRST (1000) PHYSICAL_COPY."ID"
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '123431234') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
) AbstractTable
INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
Sean