slow select in big table

From: rafalak <rafalak(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: slow select in big table
Date: 2009-04-02 20:48:00
Message-ID: 39582c42-c0ae-44ba-a34e-dc1a1d2d5bd3@g19g2000yql.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello i have big table
80mln records, ~6GB data, 2columns (int, int)

if query
select count(col1) from tab where col2=1234;
return low records (1-10) time is good 30-40ms
but when records is >1000 time is >12s

How to increse performace ?

my postgresql.conf
shared_buffers = 810MB
temp_buffers = 128MB
work_mem = 512MB
maintenance_work_mem = 256MB
max_stack_depth = 7MB
effective_cache_size = 800MB

db 8.3.7
server, atlon dual-core 2,0Ghz, 2GB RAM, SATA

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ricardo Fuentes 2009-04-02 21:05:03 Hello, i want to subscribe...
Previous Message dfx 2009-04-02 20:43:43 Thank you