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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
