Re: postgresql is slow with larger table even it is in RAM

Hello Sathiya,

1st: you should not use a ramdisk for this, it will slow things down as
compared to simply having the table on disk. Scanning it the first time
when on disk will load it into the OS IO cache, after which you will get
memory speed.

2nd: you should expect the ³SELECT COUNT(*)² to run at a maximum of about
350 ­ 600 MB/s (depending on PG version and CPU speed). It is CPU speed
limited to that rate of counting rows no matter how fast your IO is.

So, for your 700 MB table, you should expect a COUNT(*) to run in about 1-2
seconds best case. This will approximate the speed at which other queries
can run against the table.

- Luke

On 3/25/08 1:35 AM, "sathiya psql" <sathiya(dot)psql(at)gmail(dot)com> wrote:

> Dear Friends,
> I have a table with 32 lakh record in it. Table size is nearly 700 MB,
> and my machine had a 1 GB + 256 MB RAM, i had created the table space in RAM,
> and then created this table in this RAM.
>
> So now everything is in RAM, if i do a count(*) on this table it returns
> 327600 in 3 seconds, why it is taking 3 seconds ????? because am sure that no
> Disk I/O is happening. ( using vmstat i had confirmed, no disk I/O is
> happening, swap is also not used )
>
> Any Idea on this ???
>
> I searched a lot in newsgroups ... can't find relevant things.... ( because
> everywhere they are speaking about disk access speed, here i don't want to
> worry about disk access )
>
> If required i will give more information on this.
>
>
>