Hello Michael and Dmitry,

Michael, the index on PHYSICAL_COPY."COMMIT_NUMBER" is ascending, so in
theory it should be used.

Dmitry, I simplified the query by removing one join and selecting just one
field. The query is still very slow but the temporary sort file was only
130MB. This is good, but may not be easy to implement with our ORM layer.

So, I changed INNER with LEFT JOIN and got the query to run in 140ms. But
looking at the query plan it seems like it's doing sorting before joining.
Can you tell me if the following query does FIRST(1000) on PHYSICAL_COPY
table before joining it with COPY or FIRST(1000) is done after the join?

SELECT FIRST (1000) COPY."ID"
FROM PHYSICAL_COPY LEFT JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_DIRTY" = 1) AND (COPY."SOURCE_ID" =
'5d74876f-1b14-466a-aeee-f000b8b16512') AND (PHYSICAL_COPY."COMMIT_NUMBER"

>= 1000)

ORDER BY PHYSICAL_COPY."COMMIT_NUMBER" ASC
Prepare time: 0.015s
Field #01: COPY.ID Alias:ID Type:STRING(38)
PLAN JOIN (PHYSICAL_COPY ORDER IDX_214/CmDhH936xtHXcXNQKg== INDEX
(IDX_214/CmDhH936xtHXcXNQKg==, IDX_4I5n6Ay0py37rBgakWZS3Q==), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==))
Executing...
Done.
1815 fetches, 0 marks, 933 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 247 index, 0 seq.
Delta memory: 1403300 bytes.
Total execution time: 0.140s

Thanks,

Alec

On Sat, Jun 2, 2012 at 3:11 AM, Dmitry Kuzmenko <kdv@...> wrote:

> **
>
>
> 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
>
>
>


[Non-text portions of this message have been removed]