> Quite possibly, but it could be any of a number of other things, > like a type mismatch. It might be best to rule out other causes. If > you post the new query and EXPLAIN ANALYZE output, along with the > settings you have now adopted, someone may be able to spot > something. It wouldn't hurt to repeat OS and hardware info with it > so people have it handy for reference. > > Sorry for the late reply. To summarise,
The version is PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit. Server specs are: - Centos, ext4 - 24GB memory - 6 cores hyper-threaded (Intel(R) Xeon(R) CPU E5645). - raid 10 on 4 sata disks Config changes are - shared_buffers = 6GB - work_mem = 80MB - maintenance_work_mem = 3GB - effective_cache_size = 22GB - seq_page_cost = 0.1 - random_page_cost = 0.1 - cpu_tuple_cost = 0.05 - geqo = off The query is, explain (analyze, buffers) SELECT * FROM IM_Match_Table smalltable inner join invtran bigtable on bigtable.invtranref = smalltable.invtranref The result is, "QUERY PLAN" "Nested Loop (cost=0.00..341698.92 rows=48261 width=171) (actual time=0.042..567.980 rows=48257 loops=1)" " Buffers: shared hit=242267" " -> Seq Scan on im_match_table smalltable (cost=0.00..2472.65 rows=48261 width=63) (actual time=0.006..8.230 rows=48261 loops=1)" " Buffers: shared hit=596" " -> Index Scan using pk_invtran on invtran bigtable (cost=0.00..6.98 rows=1 width=108) (actual time=0.010..0.011 rows=1 loops=48261)" " Index Cond: (invtranref = smalltable.invtranref)" " Buffers: shared hit=241671" "Total runtime: 571.662 ms"
