>
> The indexes are:
>
> CREATE INDEX file_info_#_display_idx ON file_info_# (file_type,
> file_parent_dir, file_name);
> CREATE INDEX file_set_#_sync_idx ON file_set_# (fs_name, fs_parent_dir,
> fs_type)
>
> Are these not effective for the second query? If not, what should I
> change or add? If so, would you have any insight into why there is such an
> incredible difference in performance?
>
I didn't look at your indexes closely enough. When you have concatenated
index, you want to have the most selective colum first. I guess that
file_type is not very selective. file_name is probably the most selective.
In the above, the index on file_set_# is optimal. The index on file_info_#
is suboptimal.
However, if the query is doing a hash join or sort merge, an index is not
used so the index doesn't matter. However, you probably do other queries
that do use the index so it should be fixed.
Vincent