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.psql@xxxxxxxxx> 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.
