Why is LEFT JOIN is slower?

Question

Given the following 3 tables, branches, branch_sale_targets and sales_report

\d branches;
                Table "public.branches"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 branch_code | bigint |           | not null |
Indexes:
    "idx_21953433_branches_pkey" PRIMARY KEY, btree (branch_code)
Referenced by:
    TABLE "branch_sale_targets" CONSTRAINT "branch_sale_targets_branch_code_fkey" FOREIGN KEY (branch_code) REFERENCES branches(branch_code)
    TABLE "sales_report" CONSTRAINT "sales_report_branch_code_fkey" FOREIGN KEY (branch_code) REFERENCES branches(branch_code)

\d branch_sale_targets
          Table "public.branch_sale_targets"
   Column    |  Type  | Collation | Nullable | Default 
-------------+--------+-----------+----------+---------
 branch_code | bigint |           | not null |
 target      | real   |           |          |
Indexes:
    "idx_21953439_idx_branch_sale_targets_code" btree (branch_code)
    "idx_21953439_idx_branch_sale_targets_month" btree (month)
Foreign-key constraints:
    "branch_sale_targets_branch_code_fkey" FOREIGN KEY (branch_code) REFERENCES branches(branch_code)

\d sales_report 
                   Table "public.sales_report"
         Column         |  Type  | Collation | Nullable | Default 
------------------------+--------+-----------+----------+---------
 line_id                | text   |           | not null |
 branch_code            | bigint |           | not null |
 gross_amount           | real   |           |          | 
 ...(34 fields are ignore)
Indexes:
    "idx_21953445_sqlite_autoindex_sales_report_1" PRIMARY KEY, btree (line_id)
    "idx_21953445_idx_sales_report_branch_code" btree (branch_code)
Foreign-key constraints:
    "sales_report_branch_code_fkey" FOREIGN KEY (branch_code) REFERENCES branches(branch_code)

When I issue the following query it took 110.111 ms

SELECT sum(gross_amount)
FROM sales_report;

Time: 110.111 ms

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Aggregate  (cost=33191.16..33191.17 rows=1 width=4)
   ->  Seq Scan on sales_report  (cost=0.00..31861.53 rows=531853 width=4)
(2 rows)

However, when I try to join sales_report with branch_sale_targets, it is 10 times slower than the above query.

SELECT sum(gross_amount)
FROM sales_report
LEFT JOIN branch_sale_targets on sales_report.branch_code = branch_sale_targets.branch_code;

Time: 690.012 ms

                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Aggregate  (cost=120977.21..120977.22 rows=1 width=4)
   ->  Hash Left Join  (cost=30.30..105021.62 rows=6382236 width=4)
         Hash Cond: (sales_report.branch_code = branch_sale_targets.branch_code)
         ->  Seq Scan on sales_report  (cost=0.00..31861.53 rows=531853 width=12)
         ->  Hash  (cost=16.80..16.80 rows=1080 width=8)
               ->  Seq Scan on branch_sale_targets  (cost=0.00..16.80 rows=1080 width=8)

I thought it must be because of the LEFT JOIN but when I tried to join sales_report with branches, it confused me even more. The planner explained it didn't have to use any join at all.

SELECT sum(sales_report.gross_amount)
FROM sales_report
LEFT JOIN branches on branches.branch_code = sales_report.branch_code

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Aggregate  (cost=33191.16..33191.17 rows=1 width=4)
   ->  Seq Scan on sales_report  (cost=0.00..31861.53 rows=531853 width=4)

Why is this behaviour and is it expected?

Answer 1

I think it is clear that performing a join that does not reduce the number of rows and then counting the rows is more expensive than just counting the number of rows, right? So the remaining question is why the last query does not perform a join. The reason is that PostgreSQL can prove that the join can be removed without a change to the query result. It seems that there is a unique constraint on branch.branch_code, but not on branch_sale_targets.branch_code.