Unexpected slow query time when joining small table with large table - Mailing list pgsql-performance

Hello all!

I'm new to postgresql, so please bear with me. First of all, I have
the following settings enabled in my postgresql.conf file:

shared_buffers = 2GB
work_mem = 2GB
maintenance_work_mem = 4GB
checkpoint_segments = 50
random_page_cost = 3.5
cpu_tuple_cost = 0.1
effective_cache_size = 48GB

I am trying to join a small table containing 127,375 records with a
larger table containing 4,830,840 records. The follow query currently
takes about 300ms:


select bigtable.a, bigtable.b, bigtable.c, count(*) from smalltable,
bigtable where bigtable.id = smalltable.user_id and smalltable.utc
between 1325376000000 and 1326721600000 group by bigtable.a,
bigtable.b, bigtable.c;


There's an index on the smalltable.utc field, and bigtable.id is the
primary key for that table.

Here's the result of running explain analyze:

 HashAggregate  (cost=227061.05..227063.45 rows=24 width=6) (actual
time=388.519..388.527 rows=24 loops=1)
   ->  Nested Loop  (cost=0.85..226511.95 rows=54911 width=6) (actual
time=0.054..359.969 rows=54905 loops=1)
         ->  Index Scan using smalltable_utc_idx on smalltable
(cost=0.42..7142.13 rows=54911 width=8) (actual time=0.034..28.803
rows=54905 loops=1)
               Index Cond: ((utc >= 1325376000000::bigint) AND (utc <=
1326721600000::bigint))
         ->  Index Scan using bigtable_pkey on bigtable
(cost=0.43..3.90 rows=1 width=14) (actual time=0.005..0.005 rows=1
loops=54905)
               Index Cond: (id = ht.user_id)
 Total runtime: 388.613 ms
(7 rows)

Time: 389.922 ms

When I do \d+, I see that bigtable is 387MB and smalltable is only
10MB. Is there a way that I can get this query to perform faster? Or
is this the type of performance that I can expect for this type of
join?

Thank you!

Ryan