Postgres 13

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 bigint primary key
 , FIRST_NAME text, LAST_NAME text
);
create table ORDERS (
   ORDER_ID bigint 
 , ORDER_DATE timestamptz, AMOUNT numeric(10,2),DESCRIPTION text
 , USER_ID bigint references USERS(USER_ID)
 , primary key (ORDER_ID,USER_ID)
) partition by hash(USER_ID);

create table ORDERS_1 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 0);
create table ORDERS_2 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 1);
create table ORDERS_3 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 2);
create table ORDERS_4 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 3);
create table ORDERS_5 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 4);
create table ORDERS_6 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 5);
create table ORDERS_7 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 6);
create table ORDERS_8 partition of ORDERS FOR VALUES WITH (MODULUS 8, REMAINDER 7);
create index ORDERS_BY_USER on ORDERS(USER_ID);
analyze;
explain (analyze,verbose,costs,buffers,timing)
select order_month,sum(amount),count(*)
from
  (
  select date_trunc('month',ORDER_DATE) order_month, USER_ID, AMOUNT
  from ORDERS join USERS using(USER_ID)
  ) user_orders
where USER_ID=42
group by order_month
;


QUERY PLAN


GroupAggregate  (cost=20.90..21.00 rows=4 width=48) (actual time=0.090..0.090 rows=0 loops=1)

  Output: (date_trunc('month'::text, orders.order_date)), sum(orders.amount), count(*)

  Group Key: (date_trunc('month'::text, orders.order_date))

  Buffers: shared hit=8

  ->  Sort  (cost=20.90..20.91 rows=4 width=24) (actual time=0.087..0.087 rows=0 loops=1)

        Output: (date_trunc('month'::text, orders.order_date)), orders.amount

        Sort Key: (date_trunc('month'::text, orders.order_date))

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=8

        ->  Nested Loop  (cost=4.33..20.86 rows=4 width=24) (actual time=0.045..0.046 rows=0 loops=1)

              Output: date_trunc('month'::text, orders.order_date), orders.amount

              Buffers: shared hit=5

              ->  Index Only Scan using users_pkey on public.users  (cost=0.15..8.17 rows=1 width=8) (actual time=0.041..0.041 rows=0 loops=1)

                    Output: users.user_id

                    Index Cond: (users.user_id = 42)

                    Heap Fetches: 0

                    Buffers: shared hit=5

              ->  Bitmap Heap Scan on public.orders_3 orders  (cost=4.18..12.64 rows=4 width=32) (never executed)

                    Output: orders.order_date, orders.amount, orders.user_id

                    Recheck Cond: (orders.user_id = 42)

                    ->  Bitmap Index Scan on orders_3_user_id_idx  (cost=0.00..4.18 rows=4 width=0) (never executed)

                          Index Cond: (orders.user_id = 42)

Planning Time: 1.208 ms

  Buffers: shared hit=67 read=13

Execution Time: 0.258 ms