Query gets slow when bind peeking is turned on

Random thoughts

• The statistics etc weren't disable on rebuild into new filegroup?
• Maintenance tasks do not look at the filegroup?
• Heavily fragmented disk?
• Try MAXDOP 1
• Exactly same fillfactor etc?

The IO looks way off so I wonder if stats or fragmentation is causing an issue

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).

The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table 
    where 
      (from_field <= 1533177608)
  )
  AND 
    (to_field   >= 1533177608) ;