| Subject | Re: [firebird-support] Slow query because an index is not picked up during sort |
|---|---|
| Author | Michael Ludwig |
| Post date | 2012-06-02T07:17:41Z |
Alec Swan schrieb am 01.06.2012 um 16:31 (-0600):
SELECT FIRST (1000)
PHYSICAL_COPY."ID", […], COPY_CLASSIFICATION."IS_DIRTY"
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" ASC
The problem appears to be with the ORDER BY clause.
INDEX directionality, why and how? What about KEYs? - milu 11.05.10
http://tech.groups.yahoo.com/group/firebird-support/message/108428
What does SHOW INDEX say for the colum in question?
Consider if the following may be the reason your index isn't used:
SQL> show index;
TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID)
SQL> set planonly on;
SQL> select * from table2 order by id desc;
PLAN (TABLE2 ORDER TABLE2_ID_IDX)
SQL> select * from table2 order by id asc;
PLAN SORT ((TABLE2 NATURAL))
SQL> -- Index not used for sorting ASC :(
SQL> -- Now create a second, ASCENDING INDEX on the ID column:
SQL> create ascending index table2_id_asc on table2(id);
SQL> show index;
TABLE2_ID_ASC INDEX ON TABLE2(ID)
TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID)
SQL> select * from table2 order by id desc;
PLAN (TABLE2 ORDER TABLE2_ID_IDX)
SQL> select * from table2 order by id asc;
PLAN (TABLE2 ORDER TABLE2_ID_ASC)
--
Michael Ludwig
>Your query is essentially:
> We tracked down the query that generated a 10GB temp file running
> against a 1.5GB database. Can anybody explain why the query is not
> using an index on PHYSICAL_COPY."COMMIT_NUMBER"?
> Prepare time: 3.969s
> PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==),
> PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==),
> COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==)))
>
> Executing...
> Done.
> 6273556 fetches, 12 marks, 808464 reads, 10 writes.
> 0 inserts, 0 updates, 0 deletes, 1563789 index, 4789 seq.
> Delta memory: 607388 bytes.
> Total execution time: 0:02:04 (hh:mm:ss)
> Script execution finished.
SELECT FIRST (1000)
PHYSICAL_COPY."ID", […], COPY_CLASSIFICATION."IS_DIRTY"
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" ASC
The problem appears to be with the ORDER BY clause.
INDEX directionality, why and how? What about KEYs? - milu 11.05.10
http://tech.groups.yahoo.com/group/firebird-support/message/108428
What does SHOW INDEX say for the colum in question?
Consider if the following may be the reason your index isn't used:
SQL> show index;
TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID)
SQL> set planonly on;
SQL> select * from table2 order by id desc;
PLAN (TABLE2 ORDER TABLE2_ID_IDX)
SQL> select * from table2 order by id asc;
PLAN SORT ((TABLE2 NATURAL))
SQL> -- Index not used for sorting ASC :(
SQL> -- Now create a second, ASCENDING INDEX on the ID column:
SQL> create ascending index table2_id_asc on table2(id);
SQL> show index;
TABLE2_ID_ASC INDEX ON TABLE2(ID)
TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID)
SQL> select * from table2 order by id desc;
PLAN (TABLE2 ORDER TABLE2_ID_IDX)
SQL> select * from table2 order by id asc;
PLAN (TABLE2 ORDER TABLE2_ID_ASC)
--
Michael Ludwig