Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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
) ;
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() < 0.5 then 40000000 else random()*4000+1 end AS user_id,
    random() < 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

EXPLAIN ANALYZE
SELECT "btr_orders".*
FROM "btr_orders" 
WHERE "btr_orders"."user_id" = 40000000
  AND (
    premium_until > now()
    OR intermediate_premium_until > now()
  )
  AND (
    active_since IS NULL
    OR active_since <= now()
  )
  AND "btr_orders"."temporary" = false  
ORDER BY "btr_orders"."id" DESC
LIMIT 1;


QUERY PLAN


Limit  (cost=0.42..201.70 rows=1 width=65) (actual time=26.414..26.414 rows=1 loops=1)

  ->  Index Scan Backward using btr_orders_pkey on btr_orders  (cost=0.42..11876.08 rows=59 width=65) (actual time=26.412..26.412 rows=1 loops=1)

        Filter: ((NOT temporary) AND (user_id = 40000000) AND ((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))

        Rows Removed by Filter: 96062

Planning time: 0.150 ms

Execution time: 26.433 ms


CREATE INDEX quite_simple_idx ON btr_orders 
    (user_id, id)
WHERE NOT temporary ;
EXPLAIN ANALYZE
SELECT "btr_orders".*
FROM "btr_orders" 
WHERE "btr_orders"."user_id" = 40000000
  AND (
    premium_until > now()
    OR intermediate_premium_until > now()
  )
  AND (
    active_since IS NULL
    OR active_since <= now()
  )
  AND "btr_orders"."temporary" = false  
ORDER BY "btr_orders"."id" DESC
LIMIT 1;


QUERY PLAN


Limit  (cost=0.29..19.15 rows=1 width=65) (actual time=9.681..9.681 rows=1 loops=1)

  ->  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 <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))

        Rows Removed by Filter: 23963

Planning time: 0.179 ms

Execution time: 9.700 ms