Re: slow select in big table

On Fri, Apr 3, 2009 at 2:18 AM, rafalak <rafalak(at)gmail(dot)com> wrote:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Is the table has indexes?
Decreasing the work_mem also increase performance.
Monitor these changes by explain the query plan.

Regards,
Abbas.