Non-Clustered Indexes - keys and nonkeys
You are asking the query optimizer to produce a plan that can answer the query:

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;


The rest is fluff (including the OR pic_id = @pic_id). This will be a table scan, guaranteed, because of the low selectivity of the predicates involved (I'm sure deleted and hidden are 0/1, and map_ip I doubt it has any significant impact). The only predicate that could save the query is pic_id = @pic_id but by placing it in an OR condition you killed it's chance. No secondary index can help it, realistically. The addition of ROW_NUMBER will ad a sort, most likely, but the real damage is the scan. 

This is a lost cause. Come up with realistic requirements.