Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table USERS (
   USER_ID bigint primary key
 , FIRST_NAME text, LAST_NAME text
);

CREATE TABLE

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 TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

create index ORDERS_BY_USER on ORDERS(USER_ID);
analyze;

CREATE INDEX

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.095..0.097 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.094..0.094 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.041..0.042 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.040..0.040 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:

  Buffers: shared hit=67 read=13

Planning Time: 2.344 ms

Execution Time: 0.259 ms

EXPLAIN