Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range
Default B-Tree indexes can't operate very intelligently on range data types.  What you want is a GiST index on the range, so something like:

create index on fin_data using gist (holding_time);


For your main query, this would probably give a BitmapAnd plan which would use one index to get the list of row pointers satisfying broker_id and the other to get the list satisfying holding_time, and then take the intersection of the lists before visiting the table.  This could be quite a bit faster.  This is the most flexible case, because that gist index can be used in combination with any other indexes you have.  If that is not fast enough, you could try create extension btree_gist and then making the index:

create index on fin_data using gist (broker_id, holding_time);


This would be less flexible for other query variants, but ideal for the query you identified as your main query.

Note that GiST indexes take a lot longer to initially build than B-Tree indexes.  They are also are slower to maintain when new rows are inserted, so if your system struggles to keep up with insertion rate, you would want to benchmark it first.



Another idea which doesn't involve making more indexes, is to select just the columns you need rather than '*' in your queries.  That might allow index-only scans to be used where they otherwise cannot be.