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
Thank you, Vincent!
I didn't realize that the order made a difference. A sign of how much learning I need to do. :p For reference, I think 'file_parent_dir' and 'fs_parent_dir' are the most important because I do an 'ORDER BY [fs|file]_parent_dir ASC' on most queries. I've made the changes, thank you again!
Madison
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
