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);

insert into users (user_id,first_name,last_name)
VALUES (1,'joao','maria'),
       (2,'maria','joao');
 
insert into orders (order_id,order_date,amount,description,user_id)
 Values (1,to_date('20200505','YYYYMMDD'), 10.55 ,     'desc' , 1),
        (2,to_date('20200505','YYYYMMDD'), 12.60 ,     'desc2', 1),
        (3,to_date('20200505','YYYYMMDD'), 7.90  ,     'desc3', 2),
        (4,to_date('20200505','YYYYMMDD'), 4.12  ,     'desc4', 2) ;
2 rows affected

4 rows affected

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=2
group by order_month
;


QUERY PLAN


GroupAggregate  (cost=2.08..2.12 rows=1 width=48) (actual time=0.045..0.045 rows=1 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=2

  ->  Sort  (cost=2.08..2.09 rows=2 width=15) (actual time=0.034..0.034 rows=2 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=2

        ->  Nested Loop  (cost=0.00..2.07 rows=2 width=15) (actual time=0.024..0.027 rows=2 loops=1)

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

              Buffers: shared hit=2

              ->  Seq Scan on public.users  (cost=0.00..1.02 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

                    Output: users.user_id, users.first_name, users.last_name

                    Filter: (users.user_id = 2)

                    Rows Removed by Filter: 1

                    Buffers: shared hit=1

              ->  Seq Scan on public.orders_3 orders  (cost=0.00..1.02 rows=2 width=23) (actual time=0.005..0.006 rows=2 loops=1)

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

                    Filter: (orders.user_id = 2)

                    Buffers: shared hit=1

Planning Time: 0.720 ms

  Buffers: shared hit=110 read=4

Execution Time: 0.118 ms