Postgres planer JOIN removal
If you're testing with 50,000 rows in data_dynamic, I'd expect your inner join to return 50,000 rows. It seems to be returning about 10,000. There's a foreign key constraint—this seems unusual to me.

I loaded a bunch of random data, and ran this.

analyze data_dynamic;
analyze data_static;
explain analyze 
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;


And here's what my box returned. (PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
  -&gt;  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
        Merge Cond: (s.id = d.id)
        -&gt;  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
        -&gt;  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms


So it looks like the query planner is capable of coming up with a good plan for these tables and this quantity of data. 

Does this mean your problem is either with hardware or with PostgreSQL configuration? I'm not sure it's that simple.

Later . . .

I was right; it's both simpler and not that simple.

Based on these snippets from PostgreSQL mailing lists, I'm going to say that the query planner simply doesn't yet support removing inner joins.

From the pgsql-performance mailing list, 02-Mar-2011


  The planner doesn't currently make any deductions whatsoever from the
  presence of a foreign key constraint; and even if it did, I'm not sure
  that this would help it decide that a join order constraint could
  safely be dropped.


And from pgsql-hackers, 12-Dec-2010


  I'm not going to argue that careful analysis isn't needed before doing
  something like this - and, in particular, if we ever get inner-join
  removal, which I'm still hoping to do at some point, . . .