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

     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.

On 2008-03-25 09:51, sathiya psql wrote:

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

PostgreSQL needs to scan the whole table. And 700MB is a lot of data
even when in RAM. Check this:

$ dd if=/dev/zero bs=1M count=700 | cat > /dev/null
734003200 bytes (734 MB) copied, 1.38732 s, 529 MB/s

This command will just copy 700MB of zeros around in RAM, and it takes
over 1s to do it on my Pentium D 3,4GHz. PostgreSQL would need to do
much more with this data.

Maybe you just got bitten by count(*) gotcha:
http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7
Maybe you'll get by with an estimate.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

Please stop reposting your questions to multiple groups.  Since all your
questions are about performance, please stick to the pgsql-performance
list.  Posting to pgsql-sql is not really appropriate, and in
pgsql-admin you're totally off-topic.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

ok