Benchmarking of SQL data crunching using a common datum
There is a bug in your second version. Here is one row from the result:

1;0;2;0


That is, Serial_Number 1 has zero ones, two twos, and zero threes.

But let's do a quick sanity check:

select count(*) from seeded
where serial_number = 1 and status = 'one';


This query returns 100000.

There are also rows with the same Serial_Number, which I think is not what you want.

I think you want a query like this:

select 
    seeded.Serial_Number, 
    sum(case when seeded.Status = 'one' then 1 else 0 end) as ones,
    sum(case when seeded.Status = 'two' then 1 else 0 end) as twos,
    sum(case when seeded.Status = 'three' then 1 else 0 end) as threes
from seeded
group by seeded.Serial_Number;


This takes ~2.1s on my machine.

Just for fun, let's look at what pgAdmin's EXPLAIN visualisation looks like for this query



versus the 30m query