> 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"

Reply via email to