adding an index on different fields
As already mentioned in the comments by @JacobH, consider putting btr_orders.id in an index.

I have assumed you have a scenario similar to this one:

 CREATE TABLE users
 (
     user_id integer PRIMARY KEY
 ) ;

 CREATE TABLE btr_orders
 (
     id SERIAL PRIMARY KEY,
     active_since timestamp without time zone,
     premium_until timestamp without time zone,
     intermediate_premium_until timestamp without time zone,
     user_id integer NOT NULL REFERENCES users(user_id),
     temporary boolean NOT NULL DEFAULT true,
     some_cargo text
 ) ;


My assumption is that the column named id is actually your PRIMARY KEY, and declared it as such. By doing so, I automatically get an index on that column.

I simulate users and orders:

 INSERT INTO users (user_id)
 SELECT generate_series(1, 5000) AS user_id ;     
 INSERT INTO users (user_id) VALUES (40000000) ;



5000 rows affected
1 rows affected
 

 INSERT INTO btr_orders
     (active_since, premium_until, intermediate_premium_until, user_id, temporary)
 SELECT
     active_since, 
     active_since + interval '1 day' AS premium_until,
     active_since + interval '12 hours' AS intermediate_premium_until,
     case when random() &lt; 0.5 then 40000000 else random()*4000+1 end AS user_id,
     random() &lt; 0.5 AS temporary 
 FROM
     generate_series(date '2017-01-01', date '2017-12-31', interval '3 minutes') AS x(active_since);



174721 rows affected
 

The execution plan for your query is:
 
 | QUERY PLAN                                                                                                                                                                               |
 | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.42..201.70 rows=1 width=65) (actual time=25.518..25.519 rows=1 loops=1)                                                                                                   |
 |   -&gt;  Index Scan Backward using btr_orders_pkey on btr_orders  (cost=0.42..11876.08 rows=59 width=65) (actual time=25.517..25.517 rows=1 loops=1)                                        |
 |         Filter: ((NOT temporary) AND (user_id = 40000000) AND ((active_since IS NULL) OR (active_since &lt;= now())) AND ((premium_until &gt; now()) OR (intermediate_premium_until &gt; now()))) |
 |         Rows Removed by Filter: 96067                                                                                                                                                    |
 | Planning time: 0.143 ms                                                                                                                                                                  |
 | Execution time: 25.536 ms                                                                                                                                                                |
 

It takes miliseconds.

You can improve the performance by using a relatively simple partial index:

 CREATE INDEX quite_simple_idx ON btr_orders 
     (user_id, id)
 WHERE NOT temporary ;


This index will take care of:


Filtering out all rows where temporary is true. The index will only be used if you filter by temporary = false as one of the factors in your WHERE conditions.
Letting the database quickly seek for user_id = xxxxx. 
Once this user_id is found, results will retrievable sorted by id. 


This changes the execution plan to this one:


 | QUERY PLAN                                                                                                                                      |
 | :---------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.29..19.15 rows=1 width=65) (actual time=9.681..9.681 rows=1 loops=1)                                                             |
 |   -&gt;  Index Scan Backward using quite_simple_idx on btr_orders  (cost=0.29..1546.49 rows=82 width=65) (actual time=9.679..9.679 rows=1 loops=1) |
 |         Index Cond: (user_id = 40000000)                                                                                                        |
 |         Filter: (((active_since IS NULL) OR (active_since &lt;= now())) AND ((premium_until &gt; now()) OR (intermediate_premium_until &gt; now())))     |
 |         Rows Removed by Filter: 23963                                                                                                           |
 | Planning time: 0.179 ms                                                                                                                         |
 | Execution time: 9.700 ms                                                                                                                        |
 

If (and that's a big if) the simulated data I put in for test is similar enough to your actual scenario, you can improve by a factor of (about) 2. In any case, by just having an index on id, you already move from "seconds" to "miliseconds".

If you might have queries with both temporary = true and temporary = false (or temporary and NOT temporary), you can have two partial indexes, one with WHERE temporary and another with WHERE not temporary. You can also have one single index where you would add also the temporary column. Some experimentation is needed to find which is the best performing combination for every case (it depends a lot on the ratio of false/true and how often you change this value).

You can check all these simulations at dbfiddle here