| Subject | Re: [firebird-support] Slow query because an index is not picked up during sort |
|---|---|
| Author | Dmitry Kuzmenko |
| Post date | 2012-06-02T09:11:22Z |
Hello, Alec!
Saturday, June 2, 2012, 2:31:36 AM, you wrote:
AS> We tracked down the query that generated a 10GB temp file running
AS> against a 1.5GB database. Can anybody explain why the query is not
AS> using an index on PHYSICAL_COPY."COMMIT_NUMBER"?
the sort file is big because you are trying to get lot of
fields. Are they all needed? For example, if you have
varchar(1000), and you store only <=100 characters in it,
on disk it will be not more than 100 characters, but sort
will expand it to 1000, because sort can sort only fixed-sized
records.
So, for queries with plan sort the less number of fields is selected, then better.
--
Dmitry Kuzmenko, www.ib-aid.com
Saturday, June 2, 2012, 2:31:36 AM, you wrote:
AS> We tracked down the query that generated a 10GB temp file running
AS> against a 1.5GB database. Can anybody explain why the query is not
AS> using an index on PHYSICAL_COPY."COMMIT_NUMBER"?
the sort file is big because you are trying to get lot of
fields. Are they all needed? For example, if you have
varchar(1000), and you store only <=100 characters in it,
on disk it will be not more than 100 characters, but sort
will expand it to 1000, because sort can sort only fixed-sized
records.
So, for queries with plan sort the less number of fields is selected, then better.
--
Dmitry Kuzmenko, www.ib-aid.com